A. A review of the syntax format of CONVERT (): convert (<data_ type>[length], <expression> [, Style]) second, here is a note of the meaning of style: style is the conversion style number that is provided by the SQL Server system when converting Datatime and smalldatetime data to strings, and different style numbers have different output formats; typically in the time type (datetime,smalldatetime) It is only used when the string type (Nchar,nvarchar,char,varchar) is converted to each other. The following table is the conversion style number provided by the SQL Server system:
| without century digits (yy) |
with century digits (yyyy) |
Standard |
Input/output * * |
| - |
0 or 100 (*) |
Default value |
Mon dd yyyy hh:miam (or PM) |
| 1 |
101 |
United States |
Mm/dd/yyyy |
| 2 |
102 |
Ansi |
Yy.mm.dd |
| 3 |
103 |
UK/France |
Dd/mm/yy |
| 4 |
104 |
Germany |
Dd.mm.yy |
| 5 |
105 |
Italy |
Dd-mm-yy |
| 6 |
106 |
- |
DD Mon yy |
| 7 |
107 |
- |
Mon dd, yy |
| 8 |
108 |
- |
Hh:mm:ss |
| - |
9 or 109 (*) |
Default value + milliseconds |
Mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 |
110 |
United States |
Mm-dd-yy |
| 11 |
111 |
Japan |
Yy/mm/dd |
| 12 |
112 |
Iso |
Yymmdd |
| - |
13 or 113 (*) |
European default + milliseconds |
DD Mon yyyy hh:mm:ss:mmm (24h) |
| 14 |
114 |
- |
Hh:mi:ss:mmm (24h) |
| - |
20 or 120 (*) |
ODBC specification |
YYYY-MM-DD HH:MM:SS[.FFF] |
| - |
21 or 121 (*) |
ODBC specification (with milliseconds) |
YYYY-MM-DD HH:MM:SS[.FFF] |
| - |
126 (* *) |
ISO8601 |
YYYY-MM-DD Thh:mm:ss.mmm (no spaces included) |
| - |
130* |
hijri**** |
DD Mon yyyy hh:mi:ss:mmmAM |
| - |
131* |
hijri**** |
Dd/mm/yy Hh:mi:ss:mmmAM
|
The meaning of the ' * ' in the table indicates: * The default value (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always returns century digits (yyyy).
* * input when converting to datetime, and output when converting to character data.
specifically for XML. For conversions from DateTime or smalldatetime to character data, the output format is shown in the table. For conversions from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversions from Real to character data, the output is equivalent to style 1.
Hijri is a calendar system with several forms of change, Microsoft? SQL Server? 2000 the Kuwaiti algorithm used therein.
Example code with no century digits (the comment represents a non-valid style number):
SELECT CONVERT(varchar(100), GETDATE(), 0)AS Style0
SELECT CONVERT(varchar(100), GETDATE(), 1)AS Style1
SELECT CONVERT(varchar(100), GETDATE(), 2)AS Style2
SELECT CONVERT(varchar(100), GETDATE(), 3)AS Style3
SELECT CONVERT(varchar(100), GETDATE(), 4)AS Style4
SELECT CONVERT(varchar(100), GETDATE(), 5)AS Style5
SELECT CONVERT(varchar(100), GETDATE(), 6)AS Style6
SELECT CONVERT(varchar(100), GETDATE(), 7)AS Style7
SELECT CONVERT(varchar(100), GETDATE(), 8)AS Style8
SELECT CONVERT(varchar(100), GETDATE(), 9)AS Style9
SELECT CONVERT(varchar(100), GETDATE(), 10)AS Style10
SELECT CONVERT(varchar(100), GETDATE(), 11)AS Style11
SELECT CONVERT(varchar(100), GETDATE(), 12)AS Style12
SELECT CONVERT(varchar(100), GETDATE(), 13)AS Style13
SELECT CONVERT(varchar(100), GETDATE(), 14)AS Style14
--SELECT CONVERT(varchar(100), GETDATE(), 15) AS Style15
--SELECT CONVERT(varchar(100), GETDATE(), 16) AS Style16
--SELECT CONVERT(varchar(100), GETDATE(), 17) AS Style17
--SELECT CONVERT(varchar(100), GETDATE(), 18) AS Style18
--SELECT CONVERT(varchar(100), GETDATE(), 19) AS Style19
SELECT CONVERT(varchar(100), GETDATE(), 20)AS Style21
SELECT CONVERT(varchar(100), GETDATE(), 21)AS Style21
SELECT CONVERT(varchar(100), GETDATE(), 22)AS Style22
SELECT CONVERT(varchar(100), GETDATE(), 23)AS Style23
SELECT CONVERT(varchar(100), GETDATE(), 24)AS Style24
SELECT CONVERT(varchar(100), GETDATE(), 25)AS Style25
--SELECT CONVERT(varchar(100), GETDATE(), 26) AS Style26
--SELECT CONVERT(varchar(100), GETDATE(), 27) AS Style27
--SELECT CONVERT(varchar(100), GETDATE(), 28) AS Style28
--SELECT CONVERT(varchar(100), GETDATE(), 29) AS Style29
--SELECT CONVERT(varchar(100), GETDATE(), 30) AS Style30
--SELECT CONVERT(varchar(100), GETDATE(), 31) AS Style31
Operation Result:
Five. Instance code with century digits (the comment represents a non-valid style number):
SELECT CONVERT(varchar(100), GETDATE(), 100)AS Style100
SELECT CONVERT(varchar(100), GETDATE(), 101)AS Style101
SELECT CONVERT(varchar(100), GETDATE(), 102)AS Style102
SELECT CONVERT(varchar(100), GETDATE(), 103)AS Style103
SELECT CONVERT(varchar(100), GETDATE(), 104)AS Style104
SELECT CONVERT(varchar(100), GETDATE(), 105)AS Style105
SELECT CONVERT(varchar(100), GETDATE(), 106)AS Style106
SELECT CONVERT(varchar(100), GETDATE(), 107)AS Style107
SELECT CONVERT(varchar(100), GETDATE(), 108)AS Style108
SELECT CONVERT(varchar(100), GETDATE(), 109)AS Style109
SELECT CONVERT(varchar(100), GETDATE(), 110)AS Style110
SELECT CONVERT(varchar(100), GETDATE(), 111)AS Style111
SELECT CONVERT(varchar(100), GETDATE(), 112)AS Style112
SELECT CONVERT(varchar(100), GETDATE(), 113)AS Style113
SELECT CONVERT(varchar(100), GETDATE(), 114)AS Style114
--SELECT CONVERT(varchar(100), GETDATE(), 115) AS Style115
--SELECT CONVERT(varchar(100), GETDATE(), 116) AS Style116
--SELECT CONVERT(varchar(100), GETDATE(), 117) AS Style117
--SELECT CONVERT(varchar(100), GETDATE(), 118) AS Style118
--SELECT CONVERT(varchar(100), GETDATE(), 119) AS Style119
SELECT CONVERT(varchar(100), GETDATE(), 120)AS Style121
SELECT CONVERT(varchar(100), GETDATE(), 121)AS Style121
--SELECT CONVERT(varchar(100), GETDATE(), 122) AS Style122
--SELECT CONVERT(varchar(100), GETDATE(), 123) AS Style123
--SELECT CONVERT(varchar(100), GETDATE(), 124) AS Style124
--SELECT CONVERT(varchar(100), GETDATE(), 125) AS Style125
SELECT CONVERT(varchar(100), GETDATE(), 126)AS Style126
SELECT CONVERT(varchar(100), GETDATE(), 127)AS Style127
--SELECT CONVERT(varchar(100), GETDATE(), 128) AS Style128
--SELECT CONVERT(varchar(100), GETDATE(), 129) AS Style129
SELECT CONVERT(varchar(100), GETDATE(), 130)AS Style130
SELECT CONVERT(varchar(100), GETDATE(), 131)AS Style131
Operation Result:
Convert datetime to String (convert) in SQL (GO)
