In this tutorial i will explain different date time conversions avail in SQL.
Below are the list of the different date formats using CONVERT function. As you can that the output of these date formats are of VARCHAR data types, so if you have to compare any dates that are in varchar data type then again you have to convert them in datetime data type.
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mm
Output:-
Sep 16 2011 5:55 PM
SELECT convert(varchar, getdate(), 101) --mm/dd/yyyy
Output:-
09/16/2011
SELECT convert(varchar, getdate(), 102) --yyyy.mm.dd
Output:-
2011.09.16
SELECT convert(varchar, getdate(), 106) --dd mon yyyy
Output:-
16 Sep 2011
SELECT convert(varchar, getdate(), 105) --dd-mm-yyyy
Output:-
16-09-2011
SELECT convert(varchar, getdate(), 107) --mon dd, yyyy
Output:-
Sep 16, 2011
SELECT convert(varchar, getdate(), 103) --dd/mm/yyyy
Output:-
16/09/2011
SELECT convert(varchar, getdate(), 108) --hh:mm:ss
Output:-
17:57:08