Common SQL Server date formatting
Last Update:2018-12-05
Source: Internet
Author: User
We often use a variety of date formats for some purpose. Of course, we can use string operations to construct various date formats, but why are there any ready-made functions? The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd thh: mm: Ss. mmm example: Select getdate () 11:06:08. 177 sort out the date format conversion methods that may be frequently used in SQL Server: Example: Select convert (varchar, getdate (), 120) 11:06:08 select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':', '') 20040912110608 select convert (varchar (12), getdate (), 111) 2004/09/12 select convert (varchar (12), getdate (), 112) 20040912 select convert (varchar (12), getdate (), 102) 2004.09.12 select convert (varchar (12), getdate (), 101) 09/12/2004 select convert (varchar (12 ), getdate (), 103) 12/09/2004 select convert (varchar (12), getdate (), 104) 12.09.2004 select convert (varchar (12), getdate (), 105) select convert (varchar (12), getdate (), 106) 12 09 2004 select convert (varchar (12), getdate (), 107) 09 12,200 4 select convert (varchar (12), getdate (), 108) 11:06:08 select convert (varchar (12), getdate (), 109) 09 12 2004 1 select convert (varchar (12), getdate (), 110) 09-12-2004 select convert (varchar (12), getdate (), 113) 12 09 2004 1 select convert (varchar (12), getdate (), 114) 11:06:08. 177 information in the help document without Century (yy) with Century (yyyy) standard input/output **-0 or 100 (*) default mon dd yyyy hh: miam (or pm) 1 101 USA mm/DD/yy 2 102 ANSI yy. mm. DD 3 103 British/French dd/mm/yy 4 104 German DD. mm. YY 5 105 Italian DD-mm-yy 6 106-dd mon YY 7 107-mon DD, YY 8 108-hh: mm: SS-9 or 109 (*) default + milliseconds mon dd yyyy hh: MI: SS: mmmam (or pm) 10 110 USA mm-dd-yy 11 111 Japan YY/MM/DD 12 112 ISO yymmdd-13 or 113 (*) Europe default + milliseconds dd mon yyyy hh: mm: SS: mmm (24 h) 14 114-hh: MI: SS: Mmm (24 h)-20 or 120 (*) ODBC canonical yyyy-mm-dd hh: MI: SS (24 h)-21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh: MI: Ss. mmm (24 h)-126 (***) iso8601 yyyy-mm-dd thh: mm: SS: Mmm (no spaces)-130 * Kuwaiti dd mon yyyy hh: MI: SS: mmmam-131 * Kuwaiti dd/mm/yy hh: MI: SS: mmmam