Getting Day Information
There are four functions for converting a date to a day number:
DAYOFYEAR(date)
DAYOFMONTH(date)
DAYOFWEEK(date)
WEEKDAY(date)
DAYOFYEAR(date) returns the day of the year for a given date (in numeric format); in other words,
how many days since January 1 of that year.
For example, the 1st February 2000
mysql> SELECT DAYOFYEAR(20000201);Produces:
+---------------------+
| DAYOFYEAR(20000201) |
+---------------------+
| 32 |
+---------------------+
Similarly, you can pass DAYOFYEAR a date as a string parameter:
mysql> SELECT DAYOFYEAR('2000/02/01');Produces:
+-------------------------+
| DAYOFYEAR('2000/02/01') |
+-------------------------+
| 32 |
+-------------------------+
DAYOFMONTH(date) returns the day of the month for a given date.Not surprisingly, 1st February 2000 gives us
mysql> SELECT DAYOFMONTH(20000201);Produces:
+----------------------+
| DAYOFMONTH(20000201) |
+----------------------+
| 1 |
+----------------------+Or, put another way
mysql> SELECT DAYOFMONTH('00-02-01');returns the following:
+------------------------+
| DAYOFMONTH('00-02-01') |
+------------------------+
| 1 |
+------------------------+
DAYOFWEEK(date) returns the day of the week for a given date, starting with 1=Sunday, 2=Monday,
3=Tuesday, and so on.For example, examining the same date