DATE_FORMAT Function - SQL

Overview

The DATE_FORMAT function in SQL formats a date as specified by a format string. It is useful for displaying dates in a particular format.

Example:

SELECT DATE_FORMAT('2000-01-01', 'M%, %d, %Y')

Syntax:

SELECT DATE_FORMAT(date, format)

date is the date you want to format.

format is the string specifying the desired format, using format specifiers like %Y for year, %m for month, %d for day, %W for weekday, and %M for month name.

Sample Data:

    
            first_name
            birthday
        
            Frank
            1994-03-15 00:00:00
        
            Jane
            1980-06-14 00:00:00
        
            Ashley
            1975-12-05 00:00:00
        
            Glenn
            1988-03-23 00:00:00
        
            Kelly
            1979-10-11 00:00:00
        
            Richard
            1985-08-30 00:00:00
        
            George
            1992-02-18 00:00:00
        
            Kyle
            1983-04-07 00:00:00
        
            James
            1978-09-27 00:00:00
        
            Gustavo
            1987-11-21 00:00:00

first_name	birthday
Frank	1994-03-15 00:00:00
Jane	1980-06-14 00:00:00
Ashley	1975-12-05 00:00:00
Glenn	1988-03-23 00:00:00
Kelly	1979-10-11 00:00:00
Richard	1985-08-30 00:00:00
George	1992-02-18 00:00:00
Kyle	1983-04-07 00:00:00
James	1978-09-27 00:00:00
Gustavo	1987-11-21 00:00:00

format - All available inputs

%a - Abbreviated weekday name (Sun to Sat)

%b - Abbreviated month name (Jan to Dec)

%c - Month, numeric (0 to 12)

%d - Day of the month, numeric (00 to 31)

%e - Day of the month, numeric (0 to 31)

%f - Microseconds (000000 to 999999)

%H - Hour (00 to 23)

%h - Hour (01 to 12)

%I - Hour (01 to 12)

%i - Minutes, numeric (00 to 59)

%j - Day of the year (001 to 366)

%k - Hour (0 to 23)

%l - Hour (1 to 12)

%M - Full month name (January to December)

%m - Month, numeric (00 to 12)

%p - AM or PM

%r - Time, 12-hour (hh:mm AM or PM)

%S - Seconds (00 to 59)

%s - Seconds (00 to 59)

%T - Time, 24-hour (hh:mm)

%U - Week (00 to 52), where Sunday is the first day of the week

%u - Week (00 to 52), where Monday is the first day of the week

%V - Week (01 to 53), where Sunday is the first day of the week; used with %X

%v - Week (01 to 53), where Monday is the first day of the week; used with %x

%W - Full weekday name (Sunday to Saturday)

%w - Day of the week (0 = Sunday to 6 = Saturday)

%X - Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x - Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y - Year, numeric, four digits

%y - Year, numeric, two digits

Example: Hard-coded value

In this example, we are formatting the ‘2000-01-01’ date into a different structure.

Example: Query without the WHERE Statement

In this example, we are formatting the birthday column in the company.employees table into a different structure. You could see the side by side differences in the output.

Example: Query with the WHERE Statement

In this example, we are formatting the birthday column in the company.employees table where the first_name column equals ‘Kelly,’ into a different structure. You could see the side by side differences in the output.