Updated May 15, 2023
Introduction to MySQL Date Functions
The MySQL Date function is responsible for extracting the data section from the specified date or expression DateTime. In MySQL, DATE() functions store DateTime values and help return the current date and time. Both parts are retrieved and displayed in the MySQL Server format ‘YYYY-MM-DD HH:MM: SS’. There are several significant MySQL Date Functions, but they may be a bit complicated with time functions. Some of these functions allow retrieving the date, adding a date, viewing the date format, separating date and time, calculating the number of days between two dates, and performing addition or subtraction operations for a specified time interval in a date function.
Importance of MySQL Date Functions
Let us study some of the essential DATE functions used in MySQL and applicable for different scopes in the maintenance of databases and respective tables:
- ADDDATE(): This MySQL DATE function supplements a time value and the date value.
- CURDATE(): This function returns the current date in two formats: ‘YYYY-MM-DD’ or ‘YYYYMMDD’. The format of the result depends on the argument provided to the function, which can be either a string or a numeric value.
- CURRENT_DATE(): It also works identically with the CURDATE() function and returns the current date in the two formats mentioned above.
- DATE_ADD(): This function adds time value in the form of intervals to a date. The ADDDATE() function is a substitute for this function.
- DATE_FORMAT(): This MySQL Date function organizes a date as indicated in the function’s argument. Some formats are ‘YYYY-MM-DD’, ‘YYYYMMDD’, etc.
- DATE_SUB(): This MySQL Date function subtracts a time interval value from a date.
- DATE(): This MySQL Date function allows to provide the date portion out from a given DATETIME() value expression.
- DATEDIFF(): This MySQL Date function returns the result as the number of days found between any two specified datetimes or dates.
- EXTRACT(): This function extracts a section of a date expression.
- LOCALTIME(): This MySQL Date function provides the current date and time values in a special format, i.e.,’ YYYY-MM-DD HH:MM: SS’ or ‘YYYYMMDDHHMMSS’, which depends on the type of arguments passed in the context as a string or numeric’ of the function.
- MAKEDATE(): This type of Date function returns a date by captivating a value of a year and several days where the day value should be greater than 0. The function will return a NULL value if this condition is not provided.
- MONTH(): This function returns the month’s value for the specified date, which should be between 1 to 12, i.e., Jan to Dec.
- NOW(): This MySQL Date function gives the current date and time value in a distinct format, i.e.,’ YYYY-MM-DD HH:MM:SS’ or ‘YYYYMMDDHHMMSS’, which depends on the type of arguments passed in the context as a string or numeric’ of the function.
- STR_TO_DATE(): It provides the value of datetime with the help of a string and its indicated format passed in the function’s arguments.
- SUBDATE(): It provides a time period value from a particular date.
- SYSDATE(): This function offers the current date and time value in a different format, i.e.,’ YYYY-MM-DD HH:MM: SS’ or ‘YYYYMMDDHHMMSS, which is determined by the type of arguments passed in the context as a string or numeric’ of the function.
- UTC_DATE(): Based on the arguments as a string or numeric in the function, the result, i.e., current Coordinated Universal Time (UTC) date value, is delivered in the layout like ‘YYYY-MM-DD’ or ‘YYYYMMDD’.
- GETDATE(): It returns the current date with the time.
- CONVERT(): This function helps to display the result of date and time in diverse formats.
MySQL for DATE and TIME functions
Let us view some of the Data Types formats used in MySQL for DATE and TIME functions and storing the values:
- DATE: YYYY-MM-DD
- DATETIME: YYYY-MM-DD HH:MM:SS
- TIMESTAMP: YYYY-MM-DD HH:MM:SS
- YEAR: YYYY or YY
The syntax for Date function can be defined as follows:
DATE(Expr)- ‘Expr’ denotes the required and valid expression, the date or datetime value. If not so, then the date function returns a NULL value.
- Supposing we want to show the current date, then we use the below query:
Code:
SELECT CURRENT_DATE();Output:
Code:
SELECT NOW();Output:
Syntax: Using DATE_ADD() and its syntax is mentioned as follows:
DATE_ADD(date_value, INTERVAL_Valueinterval_unit);Here, the date-value is the date to be altered, INTERVAL_Value defines the value of the date and time interval that can be both positive or negative and interval_unitis the type of date interval indicated in the function.
Code:
SELECT DATE_ADD("2020-04-30 08:30:20", INTERVAL 10 MINUTE);Output:
- When executed, the above MySQL date function returns the date value with an additional 10 minutes added to the specified date in the statement.
- Now, we will write a query where we will subtract 2 hours from a given date and time:
Code:
SELECT DATE_ADD("2020-03-25 10:15:10", INTERVAL -2 HOUR);Output:
Using the DATE_FORMAT() date function, we create the following query with the given syntax:
DATE_FORMAT(date-value, format_type);SQL Statement:
SELECT DATE_FORMAT("2020-03-25", "%M %D %Y");Output:
Using DATE_SUB() date function, we will use the subsequent MySQL query with the specified syntax:
DATE_SUB(date_value, INTERVAL_Valueinterval_unit);SQL Statement:
SELECT DATE_SUB("2020-04-28", INTERVAL 5 DAY);Output:
This function returns the date after subtracting five days from the given date. Let’s use the date function DATEDIFF to calculate the difference between two dates. The DATEDIFF function requires two expressions as inputs:
Code:
SELECT DATEDIFF ("2020-04-15 06:10:30", "2020-04-12");Output:
Conclusion
The function provides us many benefits to store the date and time of any records inserted, deleted, altered, or accessed, as well as to keep the information of Restores, Transaction processes, Rollback, Backup, business records like orders, and sales and login logout sessions and other different MySQL operations.
We use the MySQL Date functions to update the records, well-managed, and related to the date and time factors which helps find out any query in the Server.
Recommended Articles
We hope that this EDUCBA information on “MySQL Date Functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
