sql Date Time formatting

SELECT CONVERT(varchar(20), GETDATE(), 100) -- Mon DD YYYY | Oct  9 2010  8:36PM
SELECT CONVERT(varchar(8), GETDATE(), 1) -- MM/DD/YY | 10/09/10
SELECT CONVERT(varchar(10), GETDATE(), 101) -- MM/DD/YYYY | 10/09/2010
SELECT CONVERT(varchar(8), GETDATE(), 2) -- YY.MM.DD | 10.10.09
SELECT CONVERT(varchar(10), GETDATE(), 102) -- YYYY.MM.DD | 2010.10.09
SELECT CONVERT(varchar(8), GETDATE(), 3) -- DD/MM/YY | 09/10/10
SELECT CONVERT(varchar(10), GETDATE(), 103) -- DD/MM/YYYY | 09/10/2010
SELECT CONVERT(varchar(8), GETDATE(), 4) -- DD.MM.YY | 09.10.10
SELECT CONVERT(varchar(10), GETDATE(), 104) -- DD.MM.YYYY | 09.10.2010
SELECT CONVERT(varchar(8), GETDATE(), 5) -- DD-MM-YY | 09-10-10
SELECT CONVERT(varchar(10), GETDATE(), 105) -- DD-MM-YYYY | 09-10-2010
SELECT CONVERT(varchar(9), GETDATE(), 6) -- DD Mon YY | 09 Oct 10
SELECT CONVERT(varchar(11), GETDATE(), 106) -- DD Mon YYYY | 09 Oct 2010
SELECT CONVERT(varchar(10), GETDATE(), 7) -- Mon DD, YY | Oct 09, 10
SELECT CONVERT(varchar(12), GETDATE(), 107) -- Mon DD, YYYY | Oct 09, 2010
SELECT CONVERT(varchar(8), GETDATE(), 108) -- HH:MM:SS | 20:39:17
SELECT CONVERT(varchar(26), GETDATE(), 109) -- Mon DD YYYY HH:MI:SS:MMMAM (or PM) | Oct  9 2010  8:39:28:963PM
SELECT CONVERT(varchar(8), GETDATE(), 10) -- MM-DD-YY | 10-09-10
SELECT CONVERT(varchar(10), GETDATE(), 110) -- MM-DD-YYYY | 10-09-2010
SELECT CONVERT(varchar(8), GETDATE(), 11) -- YY/MM/DD | 10/10/09
SELECT CONVERT(varchar(10), GETDATE(), 111) -- YYYY/MM/DD | 2010/10/09
SELECT CONVERT(varchar(6), GETDATE(), 12) -- YYMMDD | 101009
SELECT CONVERT(varchar(8), GETDATE(), 112) -- YYYYMMDD | 20101009
SELECT CONVERT(varchar(24), GETDATE(), 113) -- DD Mon YYYY HH:MM:SS:MMM(24h) 1 | 09 Oct 2010 20:40:52:047
SELECT CONVERT(varchar(12), GETDATE(), 114) -- HH:MI:SS:MMM(24H) | 20:40:59:940
SELECT CONVERT(varchar(19), GETDATE(), 120) -- YYYY-MM-DD HH:MI:SS(24h) | 2010-10-09 20:41:08
SELECT CONVERT(varchar(23), GETDATE(), 121) -- YYYY-MM-DD HH:MI:SS.MMM(24h) | 2010-10-09 20:41:16.227
SELECT CONVERT(varchar(23), GETDATE(), 126) -- YYYY-MM-DDTHH:MM:SS:MMM | 2010-10-09T20:41:24.117
SELECT CONVERT(varchar(25), GETDATE(), 131) -- DD/MM/YYYY HH:MI:SS:MMMAM |  2/11/1431  8:41:45:600PM
SELECT SUBSTRING(CONVERT(varchar(10), GETDATE(), 120), 3, 8) -- YY-MM-DD | 10-10-09
SELECT REPLACE(CONVERT(varchar(8), GETDATE(), 11), '/', '-') -- YY-MM-DD | 10-10-09
SELECT CONVERT(varchar(10), GETDATE(), 120) -- YYYY-MM-DD | 2010-10-09
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 111), '/', '-') -- YYYY-MM-DD | 2010-10-09
SELECT RIGHT(CONVERT(varchar(8), GETDATE(), 3), 5) -- MM/YY | 10/10
SELECT SUBSTRING(CONVERT(varchar(8), GETDATE(), 3), 4, 5) -- MM/YY | 10/10
SELECT RIGHT(CONVERT(varchar(10), GETDATE(), 103), 7) -- MM/YYYY | 10/2010
SELECT CONVERT(varchar(5), GETDATE(), 11) -- YY/MM | 10/10
SELECT CONVERT(varchar(7), GETDATE(), 111) -- YYYY/MM | 2010/10
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(varchar(12), GETDATE(), 107), 9) -- Month DD, YYYY 1 | October 09, 2010
SELECT SUBSTRING(CONVERT(varchar(11), GETDATE(), 113), 4, 8) -- Mon YYYY | Oct 2010
SELECT DATENAME(MM, GETDATE()) + ' ' + CAsT(YEAR(GETDATE()) As varchar(4)) -- Month YYYY | October 2010
SELECT CAsT(DAY(GETDATE()) As varchar(2)) + ' ' + DATENAME(MM, GETDATE()) -- DD Month | 9 October
SELECT DATENAME(MM, GETDATE()) + ' ' + CAsT(DAY(GETDATE()) As varchar(2)) -- Month DD | October 9
SELECT CAsT(DAY(GETDATE()) As varchar(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAsT(YEAR(GETDATE()) As varchar(4)), 2) -- DD Month YY 1 | 9 October 10
SELECT CAsT(DAY(GETDATE()) As varchar(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAsT(YEAR(GETDATE()) As varchar(4)) -- DD Month YYYY 1 | 9 October 2010
SELECT RIGHT(CONVERT(varchar(8), GETDATE(), 5), 5) -- MM-YY | 10-10
SELECT SUBSTRING(CONVERT(varchar(8), GETDATE(), 5), 4, 5) -- MM-YY | 10-10
SELECT RIGHT(CONVERT(varchar(10), GETDATE(), 105), 7) -- MM-YYYY | 10-2010
SELECT RIGHT(CONVERT(varchar(7), GETDATE(), 120), 5) -- YY-MM | 10-10
SELECT SUBSTRING(CONVERT(varchar(10), GETDATE(), 120), 3, 5) -- YY-MM | 10-10
SELECT CONVERT(varchar(7), GETDATE(), 120) -- YYYY-MM | 2010-10
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 1), '/', '') -- MMDDYY | 100910
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') -- MMDDYYYY | 10092010
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 3), '/', '') -- DDMMYY | 091010
SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 103), '/', '') -- DDMMYYYY | 09102010
SELECT REPLACE(RIGHT(CONVERT(varchar(9), GETDATE(), 6), 6), ' ', '-') -- Mon-YY | Oct-10
SELECT REPLACE(RIGHT(CONVERT(varchar(11), GETDATE(), 106), 8), ' ', '-') -- Mon-YYYY1 | Oct-2010
SELECT REPLACE(CONVERT(varchar(9), GETDATE(), 6), ' ', '-') -- DD-Mon-YY | 09-Oct-10
SELECT REPLACE(CONVERT(varchar(11), GETDATE(), 106), ' ', '-') -- DD-Mon-YYYY 1 | 09-Oct-2010
SELECT REPLACE(CONVERT(varchar, GETDATE(), 111), '/', ' ') -- YYYY MM DD | 2010 10 09
SELECT CONVERT(varchar(7), GETDATE(), 126) -- YYYY-MM | 2010-10
SELECT RIGHT(CONVERT(varchar, GETDATE(), 106), 8) -- MON YYYY | Oct 2010
Various formats for date/time, with results after running each query today.

Updated: Saturday 9th October 2010, 11:57pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.