22 Date & Time Functions with Example Single Row Function curdate dayofmonth month now weekday year
MySQL Date & Time Functions
1. ADDDATE() function
The ADDDATE() function adds a time/date interval to a date and then returns the date.
Syntax
ADDDATE(date, INTERVAL value addunit)
OR:
ADDDATE(date, days)
2. ADDTIME() function
The ADDTIME() function adds a time interval to a time/datetime and then returns the time/datetime.
Syntax
ADDTIME(datetime, addtime)
3. CURDATE() function
The CURDATE() function returns the current date.
Note: The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric).
Note: This function equals the CURRENT_DATE() function.
Syntax
CURDATE()
4. CURRENT_TIME() function
The CURRENT_TIME() function returns the current time.
Note: The time is returned as "HH-MM-SS" (string) or as HHMMSS.uuuuuu (numeric).
Note: This function equals the CURTIME() function.
Syntax
CURRENT_TIME()
5. DATE() function
The DATE() function extracts the date part from a datetime expression.
Syntax
DATE(expression)
6. DATE_FORMAT()
The DATE_FORMAT() function formats a date as specified.
Syntax
DATE_FORMAT(date, format)
7. DAY() function
The DAY() function returns the day of the month for a given date (a number from 1 to 31).
Note: This function equals the DAYOFMONTH() function.
Syntax
DAY(date)
8. DAYNAME() function
The DAYNAME() function returns the weekday name for a given date.
Syntax
DAYNAME(date)
9. DAYOFMONTH() function
The DAYOFMONTH() function returns the day of the month for a given date (a number from 1 to 31).
Note: This function equals the DAY() function.
Syntax
DAYOFMONTH(date)
10. DAYOFWEEK() function
The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
Syntax
DAYOFWEEK(date)
11. DAYOFYEAR() function
The DAYOFYEAR() function returns the day of the year for a given date (a number from 1 to 366).
Syntax
DAYOFYEAR(date)
12. HOUR() function
The HOUR() function returns the hour part for a given date (from 0 to 838).
Syntax
HOUR(datetime)
13. MINUTE() function
The MINUTE() function returns the minute part of a time/datetime (from 0 to 59).
Syntax
MINUTE(datetime)
14. MONTH() function
The MONTH() function returns the month part for a given date (a number from 1 to 12).
Syntax
MONTH(date)
15. MONTHNAME() function
The MONTHNAME() function returns the name of the month for a given date.
Syntax
MONTHNAME(date)
16. NOW() function
The NOW() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH:MM:SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
Syntax
NOW()
17. SECOND() function
The SECOND() function returns the seconds part of a time/datetime (from 0 to 59).
Syntax
SECOND(datetime)
18. SYSDATE() function
The SYSDATE() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH:MM:SS" (string) or as YYYYMMDDHHMMSS (numeric).
Syntax
SYSDATE()
19. TIME() function
The TIME() function extracts the time part from a given time/datetime.
Note: This function returns "00:00:00" if expression is not a datetime/time, or NULL if expression is NULL.
Syntax
TIME(expression)
20. WEEKDAY()
The WEEKDAY() function returns the weekday number for a given date.
Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
Syntax
WEEKDAY(date)
21. WEEKOFYEAR() function
The WEEKOFYEAR() function returns the week number for a given date (a number from 1 to 53).
Note: This function assumes that the first day of the week is Monday and the first week of the year has more than 3 days.
Tip: Also look at the WEEK() function.
Syntax
WEEKOFYEAR(date)
22. YEAR() function
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
Syntax
YEAR(date)