T-SQL common date formats

 

 /*  Environment: win7 + SQL Server 2008 r2author: ccdesctiption: the default datetime format of the Chinese version of SQL Server is yyyy-mm-dd thh: mm: Ss. mmm example: Select getdate () 14:48:49. 913  */  Select   '  ---- YY-MM-DD ----  '   Union   All  Select   Convert ( Varchar ( 26 ),Getdate (), 120 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 111 ) Union   All  Select   Convert (Varchar ( 26 ), Getdate (), 112 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 102 ) Union  All  Select   '  ---- MM-DD-YY ----  '   Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 101 ) Union   All Select   Convert ( Varchar ( 26 ), Getdate (), 110 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (),107 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 109 ) Union   All  Select   '  ---- DD-MM-YY ---- '   Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 113 ) Union   All  Select   Convert ( Varchar (26 ), Getdate (), 103 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 104 ) Union   All  Select  Convert ( Varchar ( 26 ), Getdate (), 105 ) Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 106 )Union   All  Select   '  ---- HH-MI-SS ----  '   Union   All  Select   Convert ( Varchar ( 26 ), Getdate (), 108 ) Union  All  Select   Convert ( Varchar ( 26 ), Getdate (), 114 )

 

---- YY-MM-DD ----
2012-07-17 14:53:10

20120717
2012.07.17
---- MM-DD-YY ----
07/17/2012
07-17-2012
Jul 17,201 2
Jul 17 2012 2: 53: 10: 147Pm
---- DD-MM-YY ----
17 Jul 2012 14: 53: 10: 147
17/07/2012
172.167.2012
17-07-2012
17 Jul 2012
---- HH-MI-SS ----
14:53:10
14: 53: 10: 147