Working with Dates and Times - Database Design Manual: Using MySQL for Windows - page 120

Figure 11.7 DAYOFMONTH() and DAYOFYEAR().

aDate is any object that contains a date part. By passing the output of the NOW() func-
tion into DAYOFYEAR, you can obtain the number of days since the start of the year for a
given date. Figure 11.7 shows the output of the DAYOFYEAR() function when used in a
SELECT statement. DAYOFYEAR() is useful when calculating with dates, as it provides a
numerical version of the date to work with. You can use functions like this to calculate
things like ages, although there are other functions that make this easier.

WEEK
WEEK() returns the number of weeks since the start of the year for a given date. As usual,
all that it requires as a parameter is an object with a date part as follows:

WEEK( aDate )

So, to find the current week number we would use:

SELECT WEEK( NOW() )

Figure 11.8 shows the WEEK() function in action. You can see that it is nearly the last
week of the year at time of writing.
Incidentally, depending on the day that the year starts on, it is possible to have the begin-
nings of a week 53, so if 53 is returned from the WEEK() function, it is not an error.
Using the above format, WEEK() calculates the week number using the assumption that
the first day of the week is Sunday. If you want the calculation to be based on a Monday, you
use the amended format:

WEEK( aDate, 1 )

There are other peculiarities when the week falls around the year end; is it the first week
of this year or the last week of last year? Have a look at the Date and Time Functions sec-
tion of the MySQL online manual for further details.

YEARWEEK
YEARWEEK() returns the year and the week number as one big number. You use the func-
tion by passing it a value which has a date component as follows: