Datetime built-in functions allow you to manipulate datetime values. There are many different datetime functions. The commonly used datetime built-in functions are listed next. Note that each of them can be used with a date value, a column with date values, or an expression that contains date values.

To get the current date or time, use the following: 

• CURRENT_DATE: This returns the current date. 
• CURRENT_DATE(): This returns the date in YYYY-MM-DD format—that is, 2020-01-11. 
• CURRENT_TIME—This returns the current time.
• CURRENT_TIME(): This returns the time in HH:MM:SS format—that is, 21:09:27.
• CURRENT_TIMESTAMP: This returns the current date and time. 
• CURRENT_TIMESTAMP(): This returns the datetime in YYYY-MM-DD HH:MM:SS format—that is, 2020-01-11 21:10:23.
• NOW: This returns the current date and time in the same format as CURRENT_TIMESTAMP. 
• NOW(): This returns the datetime in YYYY-MM-DD HH:MM:SS format—that is, 2020-01-11 21:10:23.

To format dates and times, use the following: 

• DATE_FORMAT: This formats a date into a specified format.
• DATE_FORMAT(date, format): Here, date is a valid date value and format is the format you want the date in. For example, DATE_FORMAT(NOW(), %m-%d-%y') will return 01-12-20 (depending on the date you run it). To read about all the formats you can use, see the DATE_FORMAT information in the Further reading section of this chapter.

• TIME_FORMAT: This formats a time into a specified format.
• TIME_FORMAT(time, format): Here, time is a valid time value, and format is the format you want the time in. For example, TIME_FORMAT(NOW(), '%T') will return the current time in 24-hour format, 18:52:24 (depending on the time you run it). To read about all the formats you can use, see the TIME_FORMAT information in the Further reading section of this chapter.

To add or subtract dates and times, use the following: 

• ADDDATE : This adds a date interval from a date and returns that date. 
• ADDDATE(date, INTERVAL value addunit): Here, date is a valid date value, INTERVAL is a keyword that stays unchanged, value is a number, and addunit is the unit of time, such as DAY. For example, ADDDATE('2020-01-01', INTERVAL 5 DAY) will return 2020-01-06 because the ADDDATE function will add 5 days to the date specified. To read about all INTERVAL you can use, see the ADDDATE information in the Further reading section of this chapter.
• ADDTIME—This adds a date/time interval from a date and returns that date.
• ADDTIME(datetime, addtime)—Here, datetime is a valid datetime value, and addtime is the amount of time to add (positive or negative). For example, ADDTIME('2020-01-01 10:10:10', '8:10:5') will return 2020-01-01 18:20:15 because the ADDTIME function will add 8 hours, 10 minutes, and 5 seconds to the date specified. 
• DATE_SUB: This subtracts a date/time interval from a date and returns that date. 
• DATE_SUB(date, INTERVAL value subunit)—Here, date is a valid date value, INTERVAL is a keyword that stays unchanged, value is a number, and subunit is the unit of time, such as DAY. For example, DATE_SUB('2020-01-01', INTERVAL 5 DAY) will return 2019-12-27 because the DATE_SUB function will add 5 days to the date specified. DATE_SUB INTERVAL is the same as ADDDATE INTERVAL. 
• DATEDIFF: This returns the number of days between two date values. 
• DATEDIFF(date1, date2)—Here, date1 and date2 are two valid datetimes. It will calculate the difference between date1 and date2. For example, DATEDIFF('2020-01-01', '2020-01-03') results in -2. 

To get pieces of the date or time returned, use the following: 

• DATE : This returns the date part from the datetime expression. 
• DATE('2019-06-10 12:12:12') returns 2019-06-10.
• DAY: This returns the day of the month for a date.
• DAY('2019-06-10 12:12:12') returns 10. 
• DAYNAME: This returns the weekday for a date. 
• DAYNAME('2019-06-10 12:12:12') returns Monday.
• DAYOFMONTH: This returns the day of the month for a date.
• DAYOFMONTH('2019-06-10 12:12:12') returns 10. 
• DAYOFWEEK: This returns the day of the week for a date.
• DAYOFWEEK('2019-06-10 12:12:12') returns 2, where Sunday is 1, so Monday is 2, and the rest of the week counts up. 
• DAYOFYEAR—This returns the day of the year for a date. 
• DAYOFYEAR('2019-06-10 12:12:12') returns 161.
• HOUR—This returns the hour part for a date. 
• HOUR('2019-06-10 12:12:12') returns 12.
• LAST_DAY—This returns the last day of the month for a date.
• LAST_DAY('2019-06-10 12:12:12') returns 2019-06-30.
• MINUTE—This returns the minute for a date.
• MINUTE('2019-06-10 12:12:12') returns 12.
• MONTH—This returns the month for a date.
• MONTH('2019-06-10 12:12:12') returns 6.
• MONTHNAME—This returns the month name for a date.
• MONTHNAME('2019-06-10 12:12:12') returns June.
• SECOND—This returns the second for a date.
• SECOND('2019-06-10 12:12:12') returns 12.
• WEEK—This returns the week number for a date
• WEEK('2019-06-10 12:12:12') returns 23.
• WEEKDAY—This returns a weekday number for a date. 
• WEEKDAY('2019-06-10 12:12:12') returns 0, where Monday is 0 and the rest of the week counts up. 
• WEEKOFYEAR—This returns the week number for a date. 
• WEEKOFYEAR('2019-06-10 12:12:12') returns 24.

• YEAR—This returns the year part for a date.
• YEAR('2019-06-10 12:12:12') returns 2019.
• YEARWEEK—This returns the year and week number for a date. 
• YEARWEEK('2019-06-10 12:12:12') returns 201923.

Example usage with built-in datetime functions is shown in the following query: 

SELECT
DAYNAME('2019-06-10 11:12:13') as dayofweek,
MONTH('2019-06-10 11:12:13') as month,
DAY('2019-06-10 11:12:13') as day,
YEAR('2019-06-10 11:12:13') as year,
HOUR('2019-06-10 11:12:13') as hour,
MINUTE('2019-06-10 11:12:13') as minute,
SECOND('2019-06-10 11:12:13') as second

The results of the previous query are shown in the following screenshot: 

You can see that the month, day, year, hour, minute, and second built-in date functions were used on a date, and it resulted in those being extracted from the date. Also, the dayname was queried using the DAYNAME function.