Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

DATE_FORMAT

Example

Format a given date.

SELECT DATE_FORMAT(“2017-06-15”“%Y”);

Definition and Usage

The DATE_FORMAT() function formats a date according to the specified format.

Syntax

DATE_FORMAT(dateformat)

Parameter Values

Parameter

Description

date

Specify the date to be formatted.

format

Specify the format to be used. It can be one or a combination of the following values:

 

Format

Description

%a

Abbreviated weekday name (Sun to Sat)

%b

Abbreviated month name (Jan to Dec)

%c

Numeric month name (0 to 12)

%D

Numeric representation of the day of the month, followed by its ordinal suffix (1st, 2nd, 3rd, …).

%d

Day of the month as a numeric value (01 to 31)

%e

Numeric representation of the day of the month (0 to 31)

%f

Microseconds (000000 to 999999)

%H

Hour (00 to 23)

%h

Hour in 12-hour format (00 to 12)

%I

Hour in a range from 00 to 12

%i

Minutes (00 to 59).

%j

Day of the year, ranging from 001 to 366

%k

Hour represented on a scale from 0 to 23

%l

Hour represented in a range from 1 to 12.

%M

Full month name (January to December)

%m

Numeric representation of the month (00 to 12).

%p

Morning or Afternoon.

%r

Time represented in 12-hour format with AM or PM (hh:mm AM/PM)

%S

Seconds ranging from 00 to 59.

%s

Seconds represented by numbers from 00 to 59

%T

Time displayed in 24-hour format (hh:mm)

%U

Week number with Sunday as the first day (00 to 53)

%u

Week number with Monday as the first day (00 to 53).

%V

Weeks starting with Sunday as the first day (01 to 53). Utilized with %X.

%v

Weeks starting with Monday as the first day (01 to 53). Utilized with %x.

%W

Full weekday name (Sunday to Saturday)

%w

Day of the week represented with Sunday as 0 and Saturday as 6. Year corresponding to the week starting with Sunday as the first day. Utilized with %V

%X

Year corresponding to the week starting with Monday as the first day. Utilized with %v.

%Y

Year represented as a 4-digit number”

%y

Year represented as a 2-digit numeric value.

 

Technical Details

Works in:

 From MySQL version 4.0

More Examples

Example

Arrange a date in a specific format:

SELECT DATE_FORMAT(“2017-06-15”“%M %d %Y”);

Example

Arrange a date in a specific format:

SELECT DATE_FORMAT(“2017-06-15”“%W %M %e %Y”);

Example

Arrange a date in a specific format:

SELECT DATE_FORMAT(BirthDate, “%W %M %e %Y”FROM Employees;