Broad Network


MySQL Day Number of Date and Week Number of Date Functions

Date and Time in MySQL Part 4

Foreword: In this part of the series, I explain the meaning and how to obtain the day number of a Date and the week number of a date.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

This is part 4 of my series, Date and Time in MySQL. In this part of the series, I explain the meaning and how to obtain the day number of a Date and the week number of a date. You should have read the previous parts of the series in the order given, because this is a continuation. All the functions in this part of the series, take an argument of date or datetime type.

Day Number
In this series Sunday is the first day of the week and has the number 1. Monday has the number 2 until Saturday with the number 7. The number for the days of the month begin from 1 to a maximum of 31. The number for the days of the year begin from 1 to a maximum of 366.

Week Number
The week number in a year begins from 1 up to a maximum of 53.

The DAYOFWEEK() Function
This function returns the day number of the week from a given date: 1 means Sunday, 2 means Monday, etc. The syntax is:

    DAYOFWEEK(date)

The value of date here can be of type datetime or simply date. The following statements illustrate this:

    SELECT dayofweek('2012-06-06 09:17:35');

    SELECT dayofweek('2012-06-06');

The return number for the two statements is 4, for a Wednesday in the month of 06 (June) of the year.

The DAYOFMONTH() Function
The day of the month can have any number in the range 1 to 31. To determine this from the date, you use the DAYOFMONTH() function. The syntax is:

    DAYOFMONTH(date)

The value of date here can be of type datetime or simply date. The following statements illustrate this:

    SELECT dayofmonth('2012-06-15 09:17:35');

    SELECT dayofmonth('2012-06-15');

The return value for the two statements is 15 (which could be read from the date).

The DAYOFYEAR() Function
The day of the year can have any number in the range 1 to 366. To determine this, you need the DAYOFYEAR() Function. The syntax is:

    DAYOFYEAR(date)

The value of date here can be of type datetime or simply date. The following statements illustrate this:

    SELECT dayofyear('2012-06-15 09:17:35');

    SELECT dayofyear('2012-06-15');

The return value for the two statements is 167.

The WEEKOFYEAR() Function
The week number in a year is in the range 1 to 53. To determine this, you need the WEEKOFYEAR() Function. The syntax is:

    WEEKOFYEAR(date)

The value of date here can be of type datetime or simply date. The following statements illustrate this:

    SELECT weekofyear('2012-06-15 09:17:35');

    SELECT weekofyear('2012-06-15');

The return value for the two statements is 24.

The LAST_DAY() Function
This function can be handy at times. It enables you to know the last day of the month of a given date. The syntax is:

    LAST_DAY(date)

The value of date here can be of type datetime or simply date. The following statements illustrate this:

    SELECT last_day('2012-06-15 09:17:35');

    SELECT last_day('2012-06-15');

The return value for the two statements is 2012-06-30. The last day is the last number, 30. Actually, it is the last date of the month that is returned.

That is it for this part of the series. We stop here and continue in the next part of the series.

Chrys

Related Links

Implementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course

BACK NEXT

Comments

Become the Writer's Fan
Send the Writer a Message