w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.