Archive
Date queries in SQL Server
Every day we need date in some different format. Below are some format we generally use.
|
Date Format |
SQL Statement |
Sample Output |
|
Mon DD YYYY |
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) |
Jan 11 2010 1:29PM |
|
MM/DD/YY |
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] |
11/23/95 |
|
MM/DD/YYYY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] |
11/22/1992 |
|
YY.MM.DD |
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] |
77.02.02 |
|
YYYY.MM.DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] |
1977.01.01 |
|
DD/MM/YY |
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] |
19/02/77 |
|
DD/MM/YYYY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] |
19/02/1977 |
|
DD.MM.YY |
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] |
25.12.05 |
|
DD.MM.YYYY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] |
10.10.2010 |
|
DD-MM-YY |
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] |
24-01-95 |
|
DD-MM-YYYY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] |
24-01-1992 |
|
DD Mon YY |
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] |
04 Jul 09 |
|
DD Mon YYYY |
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] |
04 Jul 2009 |
|
Mon DD, YY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] |
Jan 24, 99 |
|
Mon DD, YYYY |
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] |
Jan 24, 1999 |
|
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 2008 12:32:29:253PM |
|
MM-DD-YY |
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] |
01-01-09 |
|
MM-DD-YYYY |
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] |
01-01-2009 |
|
YY/MM/DD |
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] |
99/11/23 |
|
YYYY/MM/DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] |
1999/11/23 |
|
YYMMDD |
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] |
990124 |
|
YYYYMMDD |
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] |
19970124 |
|
DD Mon YYYY HH:MM:SS:MMM(24h) |
SELECT CONVERT(VARCHAR(24), GETDATE(), 113) |
28 Apr 200p 00:34:55:190 |
|
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) |
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) |
1977-01-01 13:42:24 |
|
YYYY-MM-DD HH:MI:SS.MMM(24h) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) |
1977-02-19 06:35:24.489 |
Cheers,
gxlive 