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?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/mysql-exercises/date-time-exercises/write-a-query-to-display-the-current-date-in-the-following-format-2.php