SQL Server - Formatting Date/Time

Formatting date time in SQL Server is done using the convert function. It requires knowing the length of the output string and the format code number.

SQL Server Date/Time Formatting - US

Format
Output
Statement
MM/DD/YY 03/04/13 CONVERT(VARCHAR(8), GETDATE(), 1)
MM/DD/YYYY 03/04/2013 CONVERT(VARCHAR(10), GETDATE(), 101)
MM-DD-YY 03-04-13 CONVERT(VARCHAR(8), GETDATE(), 10)
MM-DD-YYYY 03-04-2013 CONVERT(VARCHAR(10), GETDATE(), 110)

 

SELECT TOP 1
-- MM/DD/YY
  CONVERT(VARCHAR(8),   GETDATE(), 1)    AS "1"
-- MM/DD/YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 101)  AS "101"
-- MM-DD-YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 10)   AS "10"
-- MM-DD-YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 110)  AS "110" 
FROM AdventureWorks2008R2.Sales.Store

SQL Server Date/Time Formatting - European

Format
Output
Statement
DD/MM/YY 23/05/13 CONVERT(VARCHAR(8), GETDATE(), 3)
DD/MM/YYYY 23/05/2013 CONVERT(VARCHAR(10), GETDATE(), 103)
DD.MM.YY 23.05.13 CONVERT(VARCHAR(8), GETDATE(), 4)
DD.MM.YYYY 23.05.2013 CONVERT(VARCHAR(10), GETDATE(), 104)
DD-MM-YY 23-05-13 CONVERT(VARCHAR(8), GETDATE(), 5)
DD-MM-YYYY 23-05-2013 CONVERT(VARCHAR(8), GETDATE(), 105)
  04 Mar 2013 17:27:09:113 CONVERT(VARCHAR(24), GETDATE(), 113)

 

SELECT TOP 1
   CONVERT(VARCHAR(24), GETDATE(), 113) AS "113"
-- UK / France
-- DD/MM/YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 3)    AS "3"
-- DD/MM/YYYY 
 ,CONVERT(VARCHAR(10),  GETDATE(), 103)  AS "103"
-- German
-- DD.MM.YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 4)    AS "4"
-- DD.MM.YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 104)  AS "104" 
-- Italian
-- DD-MM-YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 5)    AS "5"
-- DD-MM-YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 105)  AS "105" 
FROM AdventureWorks2008R2.Sales.Store

SQL Server - Format Time

Format
Output
Statement
HH:MM:SS 17:30:45 CONVERT(VARCHAR(10), GETDATE(), 108)

 

SELECT TOP 1
-- HH:MM:SS
 CONVERT(VARCHAR(10),  GETDATE(), 108)  AS "108"  
FROM AdventureWorks2008R2.Sales.Store


SQL Server - Date/Time

Format
Output
Statement
Mon DD YYYY HH:MM[AM|PM] Mar 4 2013 6:00PM CONVERT(VARCHAR(20), GETDATE(), 100)
Mon DD YYYY HH:MM:SS:MMM[AM|PM] Mar 4 2013 6:00:32:330PM CONVERT(VARCHAR(26), GETDATE(), 109)
YYYY-MM-DD HH:MM:DD 2013-03-04 18:00:32 CONVERT(VARCHAR(19), GETDATE(), 120)
YYYY-MM-DD HH:MM:DD:MMM 2013-03-04 18:00:32.330 CONVERT(VARCHAR(23), GETDATE(), 121)
YYYY-MM-DDTHH:MM:DD:MMM 2013-03-04T18:00:32.330 CONVERT(VARCHAR(23), GETDATE(), 126)

 

SELECT TOP 1
-- Mon DD YYYY HH:MM[AM|PM]
  CONVERT(VARCHAR(20),  GETDATE(), 100)  AS "100"
-- Mon DD YYYY HH:MM:SS:MMM[AM|PM]
 ,CONVERT(VARCHAR(26),  GETDATE(), 109)  AS "109"
-- YYYY-MM-DD HH:MM:DD
 ,CONVERT(VARCHAR(19),  GETDATE(), 120)  AS "120"     
-- YYYY-MM-DD HH:MM:DD:MMM
 ,CONVERT(VARCHAR(23),  GETDATE(), 121)  AS "121"  
-- YYYY-MM-DDTHH:MM:DD:MMM
 ,CONVERT(VARCHAR(23),  GETDATE(), 126)  AS "126"   
FROM AdventureWorks2008R2.Sales.Store

SQL Server Date/Time Formatting - Other

Format
Output
Statement
DD Mon YY 03 Mar 13 CONVERT(VARCHAR(9), GETDATE(), 6)
DD Mon YYYY 03 Mar 2013 CONVERT(VARCHAR(11), GETDATE(), 106)
Mon DD, YY Mar 04, 13 CONVERT(VARCHAR(10), GETDATE(), 7)
Mon DD, YYYY Mar 04, 2013 CONVERT(VARCHAR(12), GETDATE(), 107)
YY.MM.DD 13.03.04 CONVERT(VARCHAR(8), GETDATE(), 2)
YYYY.MM.DD 2013.03.04 CONVERT(VARCHAR(10), GETDATE(), 102)
YY/MM/DD 13/03/04 CONVERT(VARCHAR(8), GETDATE(), 11)
YYYY/MM/DD 2013/03/04 CONVERT(VARCHAR(10), GETDATE(), 111)
YYMMDD 130304 CONVERT(VARCHAR(6), GETDATE(), 12)
YYYYMMDD 20130304 CONVERT(VARCHAR(8), GETDATE(), 112)

 

SELECT TOP 1
-- DD Mon YY
CONVERT(VARCHAR(9), GETDATE(), 6) AS "6"
-- DD Mon YYYY
,CONVERT(VARCHAR(11), GETDATE(), 106) AS "106"
-- Mon DD, YY
,CONVERT(VARCHAR(10), GETDATE(), 7) AS "7"
-- Mon DD, YYYY
,CONVERT(VARCHAR(12), GETDATE(), 107) AS "107"
-- YY.MM.DD
,CONVERT(VARCHAR(8), GETDATE(), 2) AS "2"
-- YYYY.MM.DD
,CONVERT(VARCHAR(10), GETDATE(), 102) AS "102"
-- YY/MM/DD
,CONVERT(VARCHAR(8), GETDATE(), 11) AS "11"
-- YYYY/MM/DD
,CONVERT(VARCHAR(10), GETDATE(), 111) AS "111"
-- YYMMDD
,CONVERT(VARCHAR(6), GETDATE(), 12) AS "12"
-- YYYYMMDD
,CONVERT(VARCHAR(8), GETDATE(), 112) AS "112"
FROM AdventureWorks2008R2.Sales.Store
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources