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 are used to store DateTime values and help to return the current date and time both parts when retrieved and displayed in the MySQL Server format ‘YYYY-MM-DD HH:MM: SS’. There are several significant MySQL Date Functions but it may be a bit complicated with time functions. Some of them are used to get the date, add a date, view format of the date, even separate date and time, getting the number of days existing between a couple of dates and also performing some sum or subtract operations for the provided specified time interval in a date function.
Importance of MySQL Date Functions
Let us study some of the important 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 along with the date value.
- CURDATE(): This function provides the current date where the result is defined in two different formats either ‘YYYY-MM-DD’or ‘YYYYMMDD’ that depends on the argument to be a string or numeric given in the function.
- CURRENT_DATE(): It also works identical to the CURDATE() function and returns the present date in the respective two formats mentioned above.
- DATE_ADD(): This function adds the value of time 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 argument of the function. Some formats are ‘YYYY-MM-DD’, ‘YYYYMMDD’, etc.
- DATE_SUB(): This MySQL Date function is responsible to subtract a time interval value from a date.
- DATE(): This MySQL Date function allow02s 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 is used to return date by captivating a value of a year and a number of days where the day value should be greater than 0. If this condition is not provided then, the function will return a NULL value.
- MONTH(): This type of function returns the value of the month for the specified date which should be between range from 1 to 12 i.e. Jan to Dec.
- NOW(): This MySQL Date function gives the value of current date and time 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 arguments of the function.
- SUBDATE(): It is responsible for providing a time period value from a particular date.
- SYSDATE(): This function offers the value of current date and time in a separate 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(): On the basis of 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 present 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:
- ‘Expr’ denotes the required and valid expression that is 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:
Syntax: Using DATE_ADD() and its syntax is mentioned as follows:
Here,date-value is the date to be altered, INTERVAL_Value defines the value of date and time interval that can be both positive or negative and interval_unitis the type of date interval to be indicated in the function.
SELECT DATE_ADD("2020-04-30 08:30:20", INTERVAL 10 MINUTE);
- The above date function in MySQL when executed returns the date value where 10 mins is added to the mentioned date in the statement.
- Now, we will write a query where we will subtract 2 hours from a given date and time:
SELECT DATE_ADD("2020-03-25 10:15:10", INTERVAL -2 HOUR);
Using DATE_FORMAT() date function, we create the following query with the given syntax:
SELECT DATE_FORMAT("2020-03-25", "%M %D %Y");
Using DATE_SUB() date function, we will use the subsequent MySQL query with the specified syntax:
SELECT DATE_SUB("2020-04-28", INTERVAL 5 DAY);
This function returns the date after subtracting 5 days from the given date. Let us return the difference of dates by using the date function DATEDIFF where two expressions are provided:
SELECT DATEDIFF ("2020-04-15 06:10:30", "2020-04-12");
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 process, Rollback, Backup, business records like orders, and sales and login logout sessions and other different MySQL operations.
We use this MySQL Date functions to make the records updated, well-managed, and related with the date and time factors which is helpful to find out any query in the Server.
This is a guide to MySQL Date Functions. Here we discuss an introduction, important, and examples to implement. You can also go through our other related articles to learn more –