MySQL is a very popular open-source and free database server that is ideal for small and large applications and is a relational database management system where SQL (Structured Query Language) queries or statements are used to manage and manipulate the data.
MySQL provides many built-in functions like string, date, and numeric functions. Date functions allow you to operate on the date strings like getting the current date, calculating the difference between two dates, extracting the date, year, month, hour, or time component from the date, etc. These built-in functions help you to perform tasks and operations on ‘Date strings’ easily.
MySQL Strips Time Component from Datetime
Now, extracting only date or time from date can be performed easily by using some built-in functions in MySQL. To extract only the Date component from datetime we can use the following functions,
- Using DATE() function
- Using CAST() function
- Using DATE_FORMAT() function
In this article, we explore three essential methods—DATE(), CAST(), and DATE_FORMAT()—for extracting date components from datetime strings in MySQL effortlessly.
1. Using DATE() function
The DATE() function takes a datetime expression as input and returns only the date component.
Syntax:
SELECT DATE (expression);
- The DATE() function takes the date or datetime expression as a parameter or argument and returns the date component from the expression. It only takes one argument or parameter and returns null if the expression is invalid or empty.
Example:
Let us take the datetime as ‘2024-03-15 01:05:46‘.
SELECT DATE ('2024-03-15 01:05:46') as date_component;Output:
Date function
Explanation: From the above output, we can observe that only the date component or part from the datetime string is extracted and returned. Similarly, if we provide only the date as a parameter or argument to the date function, the date component is returned i.e., the argument itself is returned as it is the date.
2. Using CAST() function
The CAST() function converts a datetime expression to the DATE datatype, effectively stripping the time component.
Syntax:
SELECT CAST(expression AS DATATYPE);
- The CAST() function converts the expression to the specified datatype. For extracting the date component from datetime we can cast the datetime expression to the date datatype.
Example:
Let us consider the datetime ‘2020-12-31 23:59:59‘, we now use the cast function to extract the date component,
SELECT CAST( '2020-12-31 23:59:59' AS DATE) AS date_component;Output:
CAST() function to extract date
Explanation: The above function also returns the date component from datetime expression where it is converting the datetime expression to date and returning the result and remember to avoid any spaces between the function name and the opening parenthesis.
3. Using DATE_FORMAT() Function
DATE_FORMAT() allows users to specify custom formats for datetime strings, enabling precise extraction of date components.
Syntax:
SELECT DATE_FORMAT(date,format);
- The DATE_FORMAT() function takes two arguments, the date string and the format to which the date needs to be converted and if any of the argument is null, the function returns null.
Example:
Let us consider the same datetime used in above example, ‘2020-12-31 23:59:59‘ and we now use the date_format() function to achieve the same result as above.
SELECT DATE_FORMAT('2020-12-31 23:59:59', '%Y-%m-%d') AS date_component;Output:
DATE_FORMAT() function
Explanation: The above query also returns the same result as in the above example i.e., the date component from datetime the format can be different and is based on what you want to extract or how you want to format the date. Note that different format specifiers are used in the second parameter of the function and the % character is required before format specifier characters.
Conclusion
By using the built-in DATE functions the date analysis operations or filtering tasks can be easily performed. The time component can be stripped from the datetime expression by using the above functions. The DATE() function is efficient and is solely used for extracting the date component from the expression and the other two functions mentioned above return the date component when certain parameters or arguments are specified and the method you want to choose depends on your preference and performance needs.