SQL Server Date/Time Formatting

Posted by Joe Enos on May 20, 2021 · 2 mins read

Here are my favorite date formats in SQL Server. These work with older versions, in case you’re not able to use the new format feature:

declare @theDate datetime = getdate();

select
-- yyyy-MM-dd
convert(char(10), @theDate, 126)

-- MM/dd/yyyy
,convert(char(10), @theDate, 101)

-- yyyy-MM-dd HH:mm:ss
,convert(char(19), @theDate, 120)

-- yyyy-MM-dd HH:mm:ss.fff
,convert(char(23), @theDate, 121)

-- yyyy-MM-ddTHH:mm:ss.fff (ISO8601)
,convert(char(23), @theDate, 126)

-- MMM _d yyyy _h:mm:ss.fff
--(if day/hour < 10, space is inserted so len is always 26)
,convert(char(26), @theDate, 109)