MySQL Date and Time Exercises: Query to display the current date in the specified format

MySQL Date Time: Exercise-14 with Solution

Write a MySQL query to display the current date in the specified format.

Code:

-- This SQL query formats the current date and time in a specific format.

SELECT 
date_format( -- Formats a date and time value according to the specified format.
CURDATE(), -- Retrieves the current date using the CURDATE() function.
        '%l:%i %p %b %e, %Y' -- Specifies the desired format for the date and time.
    );

Explanation:

• The date_format() function in MySQL is used to format a date and time value according to the specified format.
• CURDATE() retrieves the current date.
• The format string '%l:%i %p %b %e, %Y' specifies the desired format for the date and time:
• %l represents the hour in 12-hour format without leading zeros (e.g., 1, 2, 3...).
• :%i represents the minutes with leading zeros (e.g., 00, 01, 02...).
• %p represents the lowercase AM or PM.
• %b represents the abbreviated month name (e.g., Jan, Feb...).
• %e represents the day of the month with leading spaces (e.g., 1, 2, 3...).
• %Y represents the four-digit year (e.g., 2024).
• The formatted date and time are returned as the result of the query.

Sample output:

date_format(CURDATE(),'%l:%i %p %b %e, %Y')
12:00 AM Aug 30, 2017

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to display the current date in a spacific format.
Next:Write a MySQL query to get the firstname, lastname who joined in the month of June.