SQL Server

CONVERT() datetime to string – Table of output values

16. 8. 2014

I’m converting DATETIME values to string nearly every day when doing regular T-SQL development. After years I remember a lot of format parameter values to get the right string representation of input DATETIME value. But still time to time something specific is needed. This is why I have created a table of all possible combinations and I’m happy to share it with you. Just print the image and place it on your desk. Or you can copy the script below to your favorite scripts folder.
ConvertOutputValuesList

SELECT 1 Id, 0 Parameter, CONVERT(VARCHAR(30), GETDATE(), 0) [Output], 'Default' [Standard], 
       'mon dd yyyy hh:miAM (or PM)' Format UNION ALL
SELECT 2, 100, CONVERT(VARCHAR(30), GETDATE(), 100), 
      'Default', 'mon dd yyyy hh:miAM (or PM)' UNION ALL

SELECT 3, 1, CONVERT(VARCHAR(30), GETDATE(), 1), 'U.S.', 'mm/dd/yy' UNION ALL
SELECT 4, 101, CONVERT(VARCHAR(30), GETDATE(), 101), 'U.S.', 'mm/dd/yyyy' UNION ALL

SELECT 5, 2, CONVERT(VARCHAR(30), GETDATE(), 2), 'ANSI', 'yy.mm.dd' UNION ALL
SELECT 6, 102, CONVERT(VARCHAR(30), GETDATE(), 102), 'ANSI', 'yyyy.mm.dd' UNION ALL

SELECT 7, 3, CONVERT(VARCHAR(30), GETDATE(), 3), 'British/French', 'dd/mm/yy' UNION ALL
SELECT 8, 103, CONVERT(VARCHAR(30), GETDATE(), 103), 'British/French', 'dd/mm/yyyy' UNION ALL

SELECT 9, 4, CONVERT(VARCHAR(30), GETDATE(), 4), 'German', 'dd.mm.yy' UNION ALL
SELECT 10, 104, CONVERT(VARCHAR(30), GETDATE(), 104), 'German', 'dd.mm.yyyy' UNION ALL

SELECT 11, 5, CONVERT(VARCHAR(30), GETDATE(), 5), 'Italian', 'dd-mm-yy' UNION ALL
SELECT 12, 105, CONVERT(VARCHAR(30), GETDATE(), 105), 'Italian', 'dd-mm-yyyy' UNION ALL

SELECT 13, 6, CONVERT(VARCHAR(30), GETDATE(), 6), '-', 'dd mon yy' UNION ALL
SELECT 14, 106, CONVERT(VARCHAR(30), GETDATE(), 106), '-', 'dd mon yyyy' UNION ALL
 
SELECT 15, 7, CONVERT(VARCHAR(30), GETDATE(), 7), '-', 'Mon dd, yy' UNION ALL
SELECT 16, 107, CONVERT(VARCHAR(30), GETDATE(), 107), '-', 'Mon dd, yyyy' UNION ALL

SELECT 17, 8, CONVERT(VARCHAR(30), GETDATE(), 8), '-', 'hh:mi:ss' UNION ALL
SELECT 18, 108, CONVERT(VARCHAR(30), GETDATE(), 108), '-', 'hh:mi:ss' UNION ALL

SELECT 19, 9, CONVERT(VARCHAR(30), GETDATE(), 9), 'Default + milliseconds', 
       'mon dd yyyy hh:mi:ss:mmmAM (or PM)' UNION ALL
SELECT 20, 109, CONVERT(VARCHAR(30), GETDATE(), 109), 'Default + milliseconds', 
       'mon dd yyyy hh:mi:ss:mmmAM (or PM)' UNION ALL

SELECT 21, 10, CONVERT(VARCHAR(30), GETDATE(), 10), 'USA', 'mm-dd-yy' UNION ALL
SELECT 22, 110, CONVERT(VARCHAR(30), GETDATE(), 110), 'USA', 'mm-dd-yyyy' UNION ALL

SELECT 23, 11, CONVERT(VARCHAR(30), GETDATE(), 11), 'JAPAN', 'yy/mm/dd' UNION ALL
SELECT 24, 111, CONVERT(VARCHAR(30), GETDATE(), 111), 'JAPAN', 'yyyy/mm/dd' UNION ALL 

SELECT 25, 12, CONVERT(VARCHAR(30), GETDATE(), 12), 'ISO', 'yymmdd' UNION ALL
SELECT 26, 112, CONVERT(VARCHAR(30), GETDATE(), 112), 'ISO', 'yyyymmdd' UNION ALL 
 
SELECT 27, 13, CONVERT(VARCHAR(30), GETDATE(), 13), 'Europe default + milliseconds', 
       'dd mon yyyy hh:mi:ss:mmm(24h)' UNION ALL
SELECT 28, 113, CONVERT(VARCHAR(30), GETDATE(), 113), 'Europe default + milliseconds', 
       'dd mon yyyy hh:mi:ss:mmm(24h)' UNION ALL 

SELECT 29, 14, CONVERT(VARCHAR(30), GETDATE(), 14), '-', 'hh:mi:ss:mmm(24h)' UNION ALL
SELECT 30, 114, CONVERT(VARCHAR(30), GETDATE(), 114), '-', 'hh:mi:ss:mmm(24h)' UNION ALL 

SELECT 31, 20, CONVERT(VARCHAR(30), GETDATE(), 20), 'ODBC canonical', 
       'yyyy-mm-dd hh:mi:ss(24h)' UNION ALL
SELECT 32, 120, CONVERT(VARCHAR(30), GETDATE(), 120), 'ODBC canonical', 
       'yyyy-mm-dd hh:mi:ss(24h)' UNION ALL 

SELECT 33, 21, CONVERT(VARCHAR(30), GETDATE(), 21), 'ODBC canonical (with milliseconds)', 
       'yyyy-mm-dd hh:mi:ss.mmm(24h)' UNION ALL
SELECT 34, 121, CONVERT(VARCHAR(30), GETDATE(), 121), 'ODBC canonical (with milliseconds)', 
       'yyyy-mm-dd hh:mi:ss.mmm(24h)' UNION ALL 

SELECT 35, 126, CONVERT(VARCHAR(30), GETDATE(), 126), 'ISO8601', 
       'yyyy-mm-ddThh:mi:ss.mmm (no spaces)' UNION ALL 

SELECT 36, 127, CONVERT(VARCHAR(30), GETDATE(), 127), 'ISO8601 with time zone Z.', 
       'yyyy-mm-ddThh:mi:ss.mmmZ' UNION ALL 

SELECT 37, 130, CONVERT(VARCHAR(30), GETDATE(), 130), 'Hijri', 
       'dd mon yyyy hh:mi:ss:mmmAM' UNION ALL 

SELECT 38, 131, CONVERT(VARCHAR(30), GETDATE(), 131), 'Hijri', 
       'dd/mm/yy hh:mi:ss:mmmAM'
0
128
Share:

Leave a Reply