You can override the default behavior by adding the firstday parameter. Instead of 0 representing
Sunday, 1=Monday, and so on, you can set the first day to be a different day of the week.
Without the second argument, you have WEEK('2000-01-09'), a Sunday, returning 2 (Week 2 of the
year).
But with a firstday of 1, WEEK('2000-01-09',1) returns 1 because you told MySQL to start
counting from the Monday.
YEARWEEK(date[,firstday]) works in much the same way, but prepends the week with the year.
In the previous example, YEARWEEK('2000-01-09',1) returns 200001.This may sound a little academic, but internationally, not all diaries use the same convention!Current Date and Time
There are several methods for obtaining the current date and time:
NOW()/SYSDATE()/CURRENT_TIMESTAMP
CURDATE()/CURRENT_DATE
CURTIME()/CURRENT_TIME
To get the full date and time in one of the standard DATETIME formats, it's easiest to use NOW().
The answer can be returned as a string
mysql> SELECT NOW();results in the following:
+---------------------+
| NOW() |
+---------------------+
| 2000-02-05 18:05:11 |
+---------------------+or as a number:
mysql> SELECT NOW()+0;results in the following:
+----------------+
| NOW()+0 |
+----------------+
| 20000205181027 |
+----------------+
SYSDATE()and CURRENT_TIMESTAMP return exactly the same thing as NOW().
If you just want the date portion, you can use CURDATE() or CURRENT_DATE, which are identical. They
would return 2000-02-05 (or 20000205 if used in a numeric context).
If you're interested in the current time but not the date, you can use CURTIME() or CURRENT_TIME.
These are also equivalent and would return a time looking like 18:14:57 (or 181457 if used in a
numeric context).Formatting Dates and Times
DATE_FORMAT(date,format)
TIME_FORMAT(time,format)
The DATE_FORMAT and TIME_FORMAT pair of functions are useful in allowing you to display a date or
time in almost any format you choose. As well as passing a date or time parameter, you supply a
format made up of the specifiers given in Table 16.3.
Table 16.3 DATE_FORMAT and TIME_FORMAT Output Formats
format Parameter Output Format%r 12-hour time (hh:mm:ss (AM|PM))