MySQL Working With Dates | Function

MySQL Dates

The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets more complicated. 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.

MySQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

• DATE – format YYYY-MM-DD
• DATETIME – format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
• YEAR – format YYYY or YY

Note: The date data type are set for a column when you create a new table in your database!

How can get date in dd mm yyyy format in MySQL?

MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can’t. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.

Working with Dates

Look at the following table:

Orders Table

Now we want to select the records with an OrderDate of “2008-11-11” from the table above. We use the following SELECT statement:SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

The result-set will look like this:

Note: Two dates can easily be compared if there is no time component involved!

Now, assume that the “Orders” table looks like this (notice the added time-component in the “OrderDate” column):

If we use the same SELECT statement as above:SELECT * FROM Orders WHERE OrderDate=’2008-11-11′ we will get no result! This is because the query is looking only for dates with no time portion.

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.

The syntax for Date function can be defined as follows:

DATE(Expr)

• ‘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:

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,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.

Code:

SELECT DATE_ADD("2020-04-30 08:30:20", INTERVAL 10 MINUTE);

Output:

• 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:

Code:

SELECT DATE_ADD("2020-03-25 10:15:10", INTERVAL -2 HOUR);

Output:

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 5 days from the given date. Let us return the difference of dates by using the date function DATEDIFF where two expressions are provided:

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 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.