SQL Date format using T-SQL CONVERT function (More than 40 formats)
How many time you had to t sql date format date specific way such as yyyymmdd ??
Many people don’t know hidden feature of builtin T-SQL CONVERT function . This function can not only convert data types but also change format of dates/numbers. I thought about writing a small post about this function so you can bookmart this post for quick lookup which date format needs which style.
How to do t-sql date format using CONVERT function
Simple use of convert function is like this
CONVERT( Datatype_You_Want, Input_Value, Style )
So if you want YYYYMMDD for your date you can do something like this (Use Style=112 for YYYYMMDD)
if you want to remove time part from sql date (e.g. MM/dd/yyyy date format) then use Style=1 or 101 (FYI – any style above 100 includes year part as 4 digit number e.g. yyyy rather than yy)
CONVERT( varchar(8), getdate(), 112 )
Here is full list of all supported styles for date formatting . I had to write little function to loop through all formats.
declare @formatid int declare @msg varchar(200) set @formatid=0 while @formatid<200 begin set @formatid=@formatid+1 begin try set @msg='[ ' + cast(@formatid as varchar(10)) + ' ] ' + CONVERT(varchar(100),getdate(),@formatid) end try begin catch continue end catch print @msg end
--========================================================== --Syntax: CONVERT(varchar(100),getdate(), @StyleID ) --========================================================== StyleID Output Description ========================================================== [ 1 ] 05/02/14 ...................... <strong>MM/dd/yy</strong> date format [ 2 ] 14.05.02 ...................... <strong>yy.MM.dd</strong> date format [ 3 ] 02/05/14 ...................... <strong>dd/MM/yy</strong> date format [ 4 ] 02.05.14 ...................... <strong>dd.MM.yy</strong> date format [ 5 ] 02-05-14 ...................... <strong>dd-MM-yy</strong> date format [ 6 ] 02 May 14 ..................... dd Mon yy date format [ 7 ] May 02, 14 .................... Mon dd, yy date format [ 8 ] 15:20:43 ...................... <strong>HH:MIN:ss</strong> date format [ 9 ] May 2 2014 3:20:43:243PM .... Mon D yyyy h:n:s:mstt date format [ 10 ] 05-02-14 ..................... <strong>MM-dd-yy</strong> date format [ 11 ] 14/05/02 ..................... <strong>yy/MM/dd</strong> date format [ 12 ] 140502 ....................... yyMMdd date format [ 13 ] 02 May 2014 15:20:43:243 ..... dd Mon yyyy HH:nn:ss:ms date format [ 14 ] 15:20:43:243 ................. HH:nn:ss:ms sql date format [ 20 ] 2014-05-02 15:20:43 .......... yyyy-MM-dd HH:nn:ss t-sql date format [ 21 ] 2014-05-02 15:20:43.243 ...... yyyy-MM-dd HH:nn:ss.ms t-sql date format [ 22 ] 05/02/14 3:20:43 PM ......... MM/dd/yy H:n:s tt t-sql date format [ 23 ] 2014-05-02 ................... yyyy-MM-dd t-sql date format [ 24 ] 15:20:43 ..................... HH:nn:ss t-sql date format [ 25 ] 2014-05-02 15:20:43.243 ...... yyyy-MM-dd HH:nn:ss.ms t-sql date format [ 100 ] May 2 2014 3:20PM ......... Mon d yyyy h:ntt t-sql date format -- Default (0 or 100) [ 101 ] 05/02/2014 .................. MM/dd/yyyy t-sql date format -- U.S. (1 or 101) [ 102 ] 2014.05.02 .................. yyyy.MM.dd t-sql date format -- ANSI (2 or 102) [ 103 ] 02/05/2014 .................. dd/MM/yyyy t-sql date format -- British/French (3 or 103) [ 104 ] 02.05.2014 .................. dd.MM.yyyy t-sql date format -- German (4 or 104) [ 105 ] 02-05-2014 .................. dd-MM-yyyy t-sql date format -- Italian (5 or 105) [ 106 ] 02 May 2014 ................. dd Mon yyyy t-sql date format [ 107 ] May 02, 2014 ................ Mon dd, yyyy t-sql date format [ 108 ] 15:20:43 .................... HH:nn:ss t-sql date format [ 109 ] May 2 2014 3:20:43:250PM .. Mon D yyyy h:n:s:mstt t-sql date format -- Default + milliseconds ( 9 0r 109) [ 110 ] 05-02-2014 .................. MM-dd-yyyy t-sql date format -- USA [ 111 ] 2014/05/02 .................. yyyy/MM/dd -- JAPAN date format [ 112 ] 20140502 .................... yyyyMMdd -- ISO date format [ 113 ] 02 May 2014 15:20:43:283 ...................... dd Mon yyyy HH:nn:ss:ms -- Europe default + milliseconds date format [ 114 ] 15:20:43:283 ...................... HH:nn:ss:ms t-sql date format [ 120 ] 2014-05-02 15:20:43 ...................... yyyy-MM-dd HH:nn:ss t-sql date format -- ODBC canonical [ 121 ] 2014-05-02 15:20:43.283 ...................... yyyy-MM-dd HH:MM:ss.ms t-sql date format -- ODBC canonical (with milliseconds) [ 126 ] 2014-05-02T15:20:43.283 ...................... yyyy-mm-ddThh:mi:ss.ms -- ISO8601 date format [ 127 ] 2014-05-02T15:20:43.283 ...................... yyyy-mm-ddThh:mi:ss.msZ -- ISO8601 date format with time zone Z [ 130 ] 3 ??? 1435 3:20:43:283PM ...................... dd mon yyyy hh:mi:ss:MonAM t-sql date format Hijri [ 131 ] 3/07/1435 3:20:43:283PM ...................... dd/mm/yyyy hh:mi:ss:MonAM t-sql date format Hijri
Here is the reference to CONVERT function