MySQL Day Number of Date and Week Number of Date Functions
Date and Time in MySQL – Part 4
Forward: 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: 4 Sep 2012
Introduction
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 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