4/12/2013
In SQL, date formatting with versions prior to 2012 is very limited. I never can seem to remember the magic number for the right date format. Too many times I have rewritten this query below to get all the date formats, so I am posting it up for future reference.
Declare @Date as datetime = getdate()
, @i as int = 200;
Declare @tb as table (Num int, Date_Format varchar(30));
WHILE @i > 0
BEGIN
BEGIN TRY
Insert into @tb
SELECT @i, CONVERT(varchar(30), @Date, @i)
END TRY
BEGIN Catch
End Catch
Set @i -= 1
END
SELECT * FROM @tb order by 1
Thankfully, this shortcoming has been gracefully addressed in SQL 2012. I was recently looking for a format that does not exist in prior versions: M/d/yyyy. With the new FORMAT() function, this can be addressed like so:
SELECT Format(getdate(), 'M/d/yyyy')
That will give you 4/12/2013, for example. Ah, much easier than remembering arbitrary numbers!
Mark Wojciechowicz