January 21 2014

SQL Server Date Formats

Tagged Under : ,

mssql
How to format datetime value or column into a specific date format. Is the most frequently asked question by the newbie. Below table is summary of the different date formats that come from SQL Server with the CONVERT function.

Please note that the output of these date formats are VARCHAR data type, not DATETIME data type. With this in mind, any date comparisons performed after the DATETIME value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Date Format SQL Statement Output
Mon DD YYYY HH:MIAM (or PM) SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2014 1:29PM
MM/DD/YY SELECT CONVERT(VARCHAR(8), GETDATE(), 1) 11/23/98
MM/DD/YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 11/23/1998
YY.MM.DD SELECT CONVERT(VARCHAR(8), GETDATE(), 2) 98.11.23
YYYY.MM.DD SELECT CONVERT(VARCHAR(10), GETDATE(), 102) 1998.11.23
DD/MM/YY SELECT CONVERT(VARCHAR(8), GETDATE(), 3) 23/11/98
DD/MM/YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 103) 23/11/1998
DD.MM.YY SELECT CONVERT(VARCHAR(8), GETDATE(), 4) 23.11.98
DD.MM.YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 104) 23.11.1998
DD-MM-YY SELECT CONVERT(VARCHAR(8), GETDATE(), 5) 23-11-98
DD-MM-YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 23-11-1998
DD Mon YY SELECT CONVERT(VARCHAR(9), GETDATE(), 6) 04 Jul 98
DD Mon YYYY SELECT CONVERT(VARCHAR(11), GETDATE(), 106) 04 Jul 1998
Mon DD, YY SELECT CONVERT(VARCHAR(10), GETDATE(), 7) Nov 24, 98
Mon DD, YYYY SELECT CONVERT(VARCHAR(12), GETDATE(), 107) Nov 24, 1998
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM
MM-DD-YY SELECT CONVERT(VARCHAR(8), GETDATE(), 10) 11-23-98
MM-DD-YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 110) 11-23-1998
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 1998/11/23
YYMMDD SELECT CONVERT(VARCHAR(6), GETDATE(), 12) 981124
YYYYMMDD SELECT CONVERT(VARCHAR(8), GETDATE(), 112) 19981124
DD Mon YYYY HH:MM:SS:MMM(24h) SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 1998 00:34:55:190
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1998-11-23 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1998-11-23 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 23 Nov 1998 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAM SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 23/11.1998 12:39:32:429AM

Extended Date Formats

Below are some more date formats that does not come standard in SQL Server as part of the CONVERT function.
Date Format SQL Statement Output
YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8)
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘)
99-08-24
YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120)
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘)
1999-08-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5)
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5)
08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) 08/1999
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) 1999/08
Month DD, YYYY SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) August 24, 1999
Mon YYYY SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) Aug 1999
Month YYYY SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) August 1999
DD Month SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) 24 August
Month DD SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) August 24
DD Month YY SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) 24 August 99
DD Month YYYY SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) 24 August 1999
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5)
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5)
08/99
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) 08-1999
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5)
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5)
99-08
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) 1999-08
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) 082499
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) 08241999
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) 240899
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) 24081999
Mon-YY SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) Aug-99
Mon-YYYY SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) Aug-1999
DD-Mon-YY SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) 24-Aug-99
DD-Mon-YYYY SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) 24-Aug-1999

Make a Comment

*

code