May 3, 2012

SQL Server: Standard Date Format for SQL Server



Standard Date Formats
Date Format
Standard
SQL Statement
Sample Output
Mon DD YYYY 1 

HH:MIAM (or PM)
Default
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Jan 1 2005 1:29PM
MM/DD/YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
11/23/98
MM/DD/YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
11/23/1998
YY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
72.01.01
YYYY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
1972.01.01
DD/MM/YY
British/French
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
19/02/72
DD/MM/YYYY
British/French
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
19/02/1972
DD.MM.YY
German
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
25.12.05
DD.MM.YYYY
German
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
25.12.2005
DD-MM-YY
Italian
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
24-01-98
DD-MM-YYYY
Italian
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
24-01-1998
DD Mon YY 1
-
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
04 Jul 06 1
DD Mon YYYY 1
-
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
04 Jul 2006 1
Mon DD, YY 1
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Jan 24, 98 1
Mon DD, YYYY 1
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Jan 24, 1998 1
HH:MM:SS
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1
Default + 


milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
Apr 28 2006 12:32:29:253PM1
MM-DD-YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
01-01-06
MM-DD-YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
01-01-2006
YY/MM/DD
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
98/11/23
YYYY/MM/DD
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
1998/11/23
YYMMDD
ISO
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
980124
YYYYMMDD
ISO
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
19980124
DD Mon YYYY HH:MM:SS:MMM(24h)1
Europe default + milliseconds
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)
ODBC Canonical
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)
ODBC Canonical 

(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM
ISO8601
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1
Kuwaiti
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
28 Apr 2006 12:39:32:429AM1
DD/MM/YYYY HH:MI:SS:MMMAM
Kuwaiti
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
28/04/2006 12:39:32:429AM


Here are some more date formats that does not come standard in SQL Server as 
part of the CONVERT function. 




Extended Date Formats
Date Format
SQL Statement
Sample Output
YY-MM-DD


SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] 

SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] 
99-01-24
YYYY-MM-DD

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] 

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] 
1999-01-24
MM/YY
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] 

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
12/2005
YY/MM
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
99/08
YYYY/MM
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
2005/12
Month DD, YYYY 1
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) 
AS [Month DD, YYYY]
July 04, 2006 1
Mon YYYY 1
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]
Apr 2006 1
Month YYYY 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS 
[Month YYYY] 
February 2006 1
DD Month 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS 
[DD Month]
11 September 1
Month DD 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS 
[Month DD]
September 11 1
DD Month YY 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' 
+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]
19 February 72 1
DD Month YYYY 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' 
+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
11 September 2002 1
MM-YY
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] 

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]
05-2006
YY-MM
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] 

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]
2006-05
MMDDYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]
122506
MMDDYYYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
12252006
DDMMYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]
240702
DDMMYYYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]
24072002
Mon-YY 1
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS 
[Mon-YY]
Sep-02 1
Mon-YYYY 1
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS 
[Mon-YYYY]
Sep-2002 1
DD-Mon-YY 1
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]
25-Dec-05 1
DD-Mon-YYYY 1
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
25-Dec-2005 1


No comments:

Post a Comment