SQL Server Date Formats

The format function in SQL Server allows us to display the date in various formats. The SQL Server stores the date & time as integers. But in real worlds the date & time format that people use change from culture to culture. For Example some display the date as DD/MM/YYYY while some as YYYY/MM/DD. Hence it is important to format the date & time as per the users preference before displaying it to the user.

The format function was introduced in SQL 2012. Hence it will not work prior version of SQL like 2008R2, 2008 etc. For these versions use the convert function.

Syntax

We use the format function is as show below

FORMAT( value, format [, culture ] )

Where

value: a valid date expression to format
format: is the format string
culture: culture to use for formatting

Format string

There are two types of format string that is available. One is standard format string & the other one is custom format string.

Custom Format strings

The custom format strings allows us to format the date & time using the format specifiers. We can use them in various combinations to get the correct output.

For Example The following codes displays the given date in DD/MM/YYYY and YYYY/MM/DD formats.

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'

//DD/MM/YYYY 

select format(@dt,'dd/MM/yyyy')
//01/01/2006

//YYYY/MM/DD
select format(@dt,'yyyy/MM/dd')

//2006/01/01

Day, Month & Year Formats

The following tables lists all the format specifiers to format the day, month & year

Examples

Day Formats

Declare @dt DateTime
set @dt='2020-5-6 12:28 PM'


select format(@dt,'d/MM/yyyy')
//6/05/2020

select format(@dt,'dd/MM/yyyy')
// 06/05/2020

select format(@dt,'ddd/MM/yyyy')
// Wed/05/2020

select format(@dt,'dddd/MM/yyyy')
// Wednesday/05/2020
 

select format(@dt,'ddddd/MM/yyyy')
// Wednesday/05/2020

If the d format used alone, is treated as standard date and time format specifier and returns Short date pattern.

//Single d treated standard date and time format and returns date
select format(@dt,'d')            //12/16/2020

Month Formats

Months uses M. Remember m stands for minutes

Declare @dt DateTime
set @dt='2020-01-6 12:28 PM'


select format(@dt,'dd/M/yyyy')
//06/1/2020
  
select format(@dt,'dd/MM/yyyy') 
//06/01/2020


select format(@dt,'dd/MMM/yyyy')
//06/Jan/2020


select format(@dt,'dd/MMMM/yyyy')  
06/January/2020

Year formats

Declare @dt DateTime
set @dt='2006-01-01'


select format(@dt,'dd/MM/y')  
//01/01/6


select format(@dt,'dd/MM/yy') 
//01/01/06


select format(@dt,'dd/MM/yyy')
//01/01/2006


select format(@dt,'dd/MM/yyyy')
//01/01/2006


select format(@dt,'dd/MM/yyyyy') 
//01/01/02006

Time format strings

12 Hour format

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'

select format(@dt,'h:mm:ss') 
//3:08:07

select format(@dt,'hh:mm:ss')  
//03:08:07

24 Hours Format

Declare @dt DateTime


set @dt='2006-01-01 07:08:07'

select format(@dt,'H:mm:ss')  
//7:08:07

select format(@dt,'HH:mm:ss') 
//07:08:07

Minute Formats

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'

select format(@dt,'HH:m:ss') 
//15:8:07

select format(@dt,'HH:mm:ss')
15:08:07

Second Formats

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'

select format(@dt,'HH:mm:s')  
//15:08:7


select format(@dt,'HH:mm:ss')
//15:08:07

Era

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'


select format(@dt,'HH:mm:ss gg')
//15:08:07 A.D. 

Hours from UTC

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'


select format(@dt,'dd-MM-yyyy HH:mm:ss z')
//01-01-2006 15:08:07 +5

select format(@dt,'dd-MM-yyyy HH:mm:ss zz')
//01-01-2006 15:08:07 +05


select format(@dt,'dd-MM-yyyy HH:mm:ss zzz')
//01-01-2006 15:08:07 +05:30

AM/PM

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'


select format(@dt,'dd-MM-yyyy HH:mm:ss t')
//01-01-2006 15:08:07 P

select format(@dt,'dd-MM-yyyy HH:mm:ss tt')
//01-01-2006 15:08:07 PM

Other format strings

Standard date & time formats

A standard date and time format string uses a single character as the format specifier.

For Example d format specifier, displays the date in Short date pattern. This will display the date m/d/yyyy format if you are using the language us_english

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'


select format(@dt,'d')

//1/1/2006

There are two ways, in which you can you can change the language

1. Use the culture which is the third argument to the format function
2. change the language using the SET LANGUAGE TSQL command

The following changes the language to British English. Now the d format string uses the dd/MM/yyyy format.

Declare @dt DateTime
set @dt='2006-12-01 15:08:07'

SET LANGUAGE 'British English'
select format(@dt,'d')

//01/12/2006

To find out the list of supported languages use the following query.

select * from sys.syslanguages

The following uses the culture de-de.

Declare @dt DateTime
set @dt='2006-12-01 15:08:07'

SET LANGUAGE 'British English'
select format(@dt,'d','de-de')    'German'  

//12.01.2006     MM/dd/yyyy format

You can refer to list of culture codes.

The following table shows list of all standard format codes.

Examples

Declare @dt DateTime
set @dt='2006-01-01 15:08:07'


select format(@dt,'d')
//1/1/2006

select format(@dt,'D')
//Sunday, January 1, 2006

select format(@dt,'f')
//Sunday, January 1, 2006 3:08 PM

select format(@dt,'F')
//Sunday, January 1, 2006 3:08:07 PM

select format(@dt,'g')
//1/1/2006 3:08 PM

select format(@dt,'G')
//1/1/2006 3:08:07 PM


select format(@dt,'M')
//January 1

select format(@dt,'m')
//January 1

select format(@dt,'O')
//2006-01-01T15:08:07.0000000

select format(@dt,'o')
//2006-01-01T15:08:07.0000000

select format(@dt,'R')
//Sun, 01 Jan 2006 15:08:07 GMT


select format(@dt,'r')
//Sun, 01 Jan 2006 15:08:07 GMT

select format(@dt,'s')
//2006-01-01T15:08:07


select format(@dt,'t')
//3:08 PM

select format(@dt,'T')
//3:08:07 PM


select format(@dt,'u')
//2006-01-01 15:08:07Z

select format(@dt,'Y')
//January 2006

select format(@dt,'y')
//January 2006

Reference

• FORMAT (Transact-SQL)
• Standard date and time format strings
• Custom date and time format strings
• Standard TimeSpan format strings
• Custom TimeSpan format strings