w3resource

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

MySQL Date Time: Exercise-12 with Solution

Write a MySQL query to display the current date in the following format.
Sample output : Thursday 4th September 2014 00:00:00

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.
        '%W %D %M %Y %T' -- 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 '%W %D %M %Y %T' specifies the desired format for the date and time:
    • %W represents the full weekday name (e.g., Monday, Tuesday).
    • %D represents the day of the month with a suffix (e.g., 1st, 2nd, 3rd).
    • %M represents the full month name (e.g., January, February).
    • %Y represents the four-digit year (e.g., 2024).
    • %T represents the time in HH:MM:SS format.
  • The formatted date and time are returned as the result of the query.

Sample Output:

date_format(CURDATE(),'%W %D %M %Y %T')
Wednesday 30th August 2017 :00:00

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

Previous:Write a MySQL query to get the first name and hire date from employees table where hire date between '1987-06-01' and '1987-07-30'.
Next:Write a MySQL query to display the current date in a specified format.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.