SQL format of date and time, the way I like it!

Often when delivering a date in one of your Stored Procedures, you will want to format it to look like what your Product Owner calls for.  Of course this should be done in the application, but you're reading this post because you have an occasion where you need to reformat that date, don't you?!  So you might try the following.








-- Time
SELECT ltrim(right(convert(varchar(25), getdate(), 100), 7)) as [HH:MIAM]

-- Favorites
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

-- Date
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) as [Mon DD YYYY HH:MMAM]
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) as [HH:MM:SS]
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) as [Mon DD YYYY HH:MI:SS:MMMAM]

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

SELECT CONVERT(VARCHAR(24), GETDATE(), 113) as [DD Mon YYYY HH:MM:SS:MMM]
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

SELECT CONVERT(VARCHAR(19), GETDATE(), 120) as [YYYY-MM-DD HH:MI:SS(24h)]
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) as [YYYY-MM-DD HH:MI:SS.MMM(24h)]
SELECT CONVERT(VARCHAR(23), GETDATE(), 126) as [YYYY-MM-DDTHH:MM:SS:MMM]

SELECT CONVERT(VARCHAR(26), GETDATE(), 130) as [DD Mon YYYY HH:MI:SS:MMMAM]
SELECT CONVERT(VARCHAR(25), GETDATE(), 131) as [DD/MM/YYYY HH:MI:SS:MMMAM]


(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.