Home » MySQL » MySQL date/time Functions

MySQL DATE_FORMAT() Function

MySQL | DATE_FORMAT() Function: Learn about the DATE_FORMAT() function, how it works, its usages, syntax, and examples.
Submitted by Apurva Mathur, on October 11, 2022

DATE_FORMAT() Function

The DATE_FORMAT() function is used to format the given date value according to the given format, if any of the arguments is NULL, the function returns NULL.

DATE_FORMAT() Syntax

SELECT DATE_FORMAT(date, format);

DATE_FORMAT() Parameter(s)

This function requires two parameters:

• date: firstly you have to provide the date on which you want to format.
• format: Format is the arrangement you want your date to be in.

Let us the all the formats with the example,

MySQL DATE_FORMAT() Function Examples

• %a: This is a abbreviation which we can use if we want to know the weekday name i.e. Sunday to Saturday.
  
• %b: This is a abbreviation which we can use if we want to know the month name i.e. January to December.
  
• %c: This is a abbreviation which we can use if we want to know the month name numerically i.e. 1 to 12 (1=Jan,2=Feb. etc)
  
• %D: This will give you the month number with proper suffix.
  
• %d: This will give you the month number without any suffix.
  
• %e: This will give you the month number without any suffix.
  
• %f: This will give you the detail about the microseconds (000000 to 999999).
  
• %H: This will give the detail about hours in 12 hours format.
  
• %h: This will give you the result about hours in 24 hours format.
  
• %I: This will give the detail about hours in 12 hours format.
  
• %i: This will give the detail about minutes (00 to 59).
  
• %j: This will tell you the day of the year (001 to 366).
  
• %k: This will give you the result about hours in 24 hours format.
  
• %M: This will give you the full month name.
  
• %m: This will give you the month name as a numeric value.
  
• %p: This will tell you the whether it's a day or night i.e. AM OR PM.
  
• %r: This will give you the time in 12 hour AM or PM format (hh:mm:ss AM/PM).
  
• %S: This will give you the detail about the seconds (00 to 59).
  
• %s: This will give you the detail about the seconds (00 to 59).
  
• %T: This will give you the time in 24 hour format (hh:mm:ss).
  
• %U: Week where Sunday is the first day of the week (00 to 53).
  
• %u: It will tell you the Week where Monday is the first day of the week (00 to 53).
  
• %V: It will tell you the Week where Sunday is the first day of the week (01 to 53). Used with %X.
  
• %v: Week where Monday is the first day of the week (01 to 53). Used with %x.
  
• %W: Weekday name in full (Sunday to Saturday).
  
• %w: Day of the week where Sunday=0 and Saturday=6.
  
• %X: Year for the week where Sunday is the first day of the week. Used with %V.
  
• %x: Year for the week where Monday is the first day of the week. Used with %v.
  
• %Y: It tells you the year as a numeric, 4-digit value.
  
• %y: It tells you the year as a numeric, 2-digit value.
  

---