Mastering Date Functions in MySQL A Comprehensive Guide

MySQL provides a wide range of date and time functions that can be used to manipulate and format date and time values in various ways. Here are some common date functions in MySQL:

1. NOW(): returns the current date and time in YYYY-MM-DD HH:MM:SS format.
2. DATE(): extracts the date portion of a datetime or timestamp value.
3. TIME(): extracts the time portion of a datetime or timestamp value.
4. YEAR(), MONTH(), DAY(): extract the year, month, and day values from a date.
5. HOUR(), MINUTE(), SECOND(): extract the hour, minute, and second values from a time.
6. DAYNAME(): returns the name of the day of the week for a given date.
7. DATE_FORMAT(): formats a date or time value according to a specified format string.
8. DATE_ADD(), DATE_SUB(): add or subtract a specified interval of time from a date or time value.
9. TIMEDIFF(): returns the difference between two datetime or timestamp values.

Here are some examples of how these functions can be used:

Click here : wpaccuracy.com

1. SELECT NOW(); — returns the current date and time
2. SELECT DATE(‘2023-04-09 14:30:00’); — returns ‘2023-04-09’
3. SELECT TIME(‘2023-04-09 14:30:00′); — returns ’14:30:00’
4. SELECT YEAR(‘2023-04-09’); — returns ‘2023’
5. SELECT HOUR(’14:30:00′); — returns ’14’
6. SELECT DAYNAME(‘2023-04-09’); — returns ‘Sunday’
7. SELECT DATE_FORMAT(‘2023-04-09’, ‘%W %M %Y’); — returns ‘Sunday April 2023’
8. SELECT DATE_ADD(‘2023-04-09’, INTERVAL 1 DAY); — adds 1 day to ‘2023-04-09’
9. SELECT TIMEDIFF(‘2023-04-09 14:30:00’, ‘2023-04-08 13:15:00′); — returns ’25:15:00’

These functions can be used in conjunction with the SELECT statement to retrieve and manipulate date and time values from a MySQL database.