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