MySQL · December 20, 2023

MySQL Command: DATE_FORMAT()

MySQL Command: DATE_FORMAT()

MySQL is a popular open-source relational database management system that is widely used for storing and managing data. One of the essential functions provided by MySQL is the DATE_FORMAT() command, which allows users to format date and time values in various ways. In this article, we will explore the DATE_FORMAT() command and its usage in MySQL.

Introduction to DATE_FORMAT()

The DATE_FORMAT() function in MySQL is used to format date and time values according to a specified format. It takes two arguments: the date or time value to be formatted and the format string that defines the desired output format.

The format string can include various format specifiers, such as %Y for the year, %m for the month, %d for the day, %H for the hour, %i for the minutes, %s for the seconds, and many more. By combining these specifiers, you can create custom date and time formats.

Usage Examples

Let's look at some examples to understand how the DATE_FORMAT() command works:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;

This query will return the current date in the format 'YYYY-MM-DD'. For example, if the current date is January 15, 2022, the result will be '2022-01-15'.

SELECT DATE_FORMAT('2022-01-15', '%W, %M %e, %Y') AS formatted_date;

In this example, we are formatting a specific date ('2022-01-15') using the format string '%W, %M %e, %Y'. The result will be the full weekday name, followed by the full month name, the day of the month with a leading zero, and the four-digit year. For the given date, the result will be 'Saturday, January 15, 2022'.

Custom Date and Time Formats

MySQL provides a wide range of format specifiers that can be used to create custom date and time formats. Here are some commonly used specifiers:

  • %Y: Four-digit year (e.g., 2022)
  • %y: Two-digit year (e.g., 22)
  • %m: Month with leading zero (e.g., 01 for January)
  • %c: Month without leading zero (e.g., 1 for January)
  • %d: Day of the month with leading zero (e.g., 01)
  • %e: Day of the month without leading zero (e.g., 1)
  • %H: Hour in 24-hour format with leading zero (e.g., 09)
  • %h: Hour in 12-hour format with leading zero (e.g., 09 or 12)
  • %i: Minutes with leading zero (e.g., 05)
  • %s: Seconds with leading zero (e.g., 02)

These are just a few examples, and there are many more format specifiers available in MySQL. You can combine them in any order and add additional characters or symbols to create the desired format.

Conclusion

The DATE_FORMAT() command in MySQL is a powerful tool for formatting date and time values according to specific requirements. By using the format specifiers provided by MySQL, you can create custom date and time formats that suit your needs. Whether you need to display dates in a specific format or perform calculations based on formatted dates, the DATE_FORMAT() command can help you achieve your goals.

Summary

In summary, the DATE_FORMAT() command in MySQL allows users to format date and time values according to a specified format. It takes a date or time value and a format string as arguments. The format string can include various format specifiers to create custom date and time formats. By using the DATE_FORMAT() command, you can easily format dates and times in MySQL to meet your specific requirements.

For more information about VPS hosting services, visit Server.HK.

You may also like...