Different Date Time Conversion in SQL

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