SQL Server common date format conversion

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.

For example:

Select getdate ()

11:06:08. 177

I sorted 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)
12-09-2004

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

 

American Chinese in the same city www.54huaren.com