SQL Server Convert from varchar to datetime, datetime format

Here are some queries ready for varchar to datetime or datetime to varchar conversion:

Think that a datetime is a fix format that depends of the date format  in your Windows OS and if you want to customize the way to show a datetime, use a varchar. 🙂

Varchar   format From   Varchar –> datetime from   date time –> ‘Varchar’
mon dd yyyy hh:mmAM convert(datetime,’Varchar’,100) convert(Varchar,’Date’,100)
mon dd yy hh:mmAM convert(datetime,”Varchar”,0) convert(Varchar,’Date’,0)
mm/dd/yy convert(datetime,’Varchar’,1) convert(Varchar,’Date’,1)
yy.mm.dd convert(datetime,’Varchar’,2) convert(Varchar,’Date’,2)
dd/mm/yy convert(datetime,’Varchar’,3) convert(Varchar,’Date’,3)
dd.mm.yy convert(datetime,’Varchar’,4) convert(Varchar,’Date’,4)
dd-mm-yy convert(datetime,’Varchar’,5) convert(Varchar,’Date’,5)
dd mmm yy convert(datetime,’Varchar’,6) convert(Varchar,’Date’,6)
mmm dd, yy convert(datetime,’Varchar’,7) convert(Varchar,’Date’,7)
HH:mm:ss convert(datetime,’Varchar’,8) convert(Varchar,’Date’,8)
mon dd yyyy hh:mm:ss:fffAM convert(datetime,’Varchar’,9) convert(Varchar,’Date’,9)
mm-dd-yy convert(datetime,’Varchar’,10) convert(Varchar,’Date’,10)
yy/mm/dd convert(datetime,’Varchar’,11) convert(Varchar,’Date’,11)
yymmdd convert(datetime,’Varchar’,12) convert(Varchar,’Date’,12)
dd mon yyyy HH:mm:ss:fff convert(datetime,’Varchar’,13) convert(Varchar,’Date’,13)
HH:mm:ss:fff convert(datetime,’Varchar’,14) convert(Varchar,’Date’,14)
yyyy-mm-dd HH:mm:ss convert(datetime,’Varchar’,20) convert(Varchar,’Date’,20)
yyyy-mm-dd HH:mm:ss.fff convert(datetime,’Varchar’,21) convert(Varchar,’Date’,21)
mm/dd/yyyy convert(datetime,’Varchar’,101) convert(Varchar,’Date’,101)
yyyy.mm.dd convert(datetime,’Varchar’,102) convert(Varchar,’Date’,102)
dd/mm/yyyy convert(datetime,’Varchar’,103) convert(Varchar,’Date’,103)
dd.mm.yyyy convert(datetime,’Varchar’,104) convert(Varchar,’Date’,104)
dd-mm-yyyy convert(datetime,’Varchar’,105) convert(Varchar,’Date’,105)
dd mmm yyyy convert(datetime,’Varchar’,106) convert(Varchar,’Date’,106)
mmm dd, yyyy convert(datetime,’Varchar’,107) convert(Varchar,’Date’,107)
mm-dd-yyyy convert(datetime,’Varchar’,110) convert(Varchar,’Date’,110)
yyyy/mm/dd convert(datetime,’Varchar’,111) convert(Varchar,’Date’,111)
yyyymmdd convert(datetime,’Varchar’,112) convert(Varchar,’Date’,112)
yyy-mm-ddThh:mm:ss convert(datetime,’Varchar’,126) convert(Varchar,’Date’,126)
Advertisements