First, review the syntax format of CONVERT (): convert (<data_ type>[length], <expression> [, Style])
Second, here pay attention to the meaning of style: style is the conversion of datatime and smalldatetime data to a string by the SQL Server system provided by the conversion style number, different style numbers have different output formats; Generally in the time type ( Datetime,smalldatetime) and String types (Nchar,nvarchar,char,varchar) are used when converting to each other.
The following table is the conversion style number provided by the SQL Server system:
| no century Digit (yy) |
band century Digit (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 |
United Kingdom/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 defaults + 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 (excluding spaces) |
| - |
130* |
hijri**** |
DD Mon yyyy hh:mi:ss:mmmAM |
| - |
131* |
hijri**** |
Dd/mm/yy Hh:mi:ss:mmmAM
|
The meaning of ' * ' 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 the century digit (yyyy).
* * input when converting to datetime, 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.
Four, no century digit of the instance code (the annotation represents a not valid style number):
Copy Code code as follows:
SELECT CONVERT (varchar), GETDATE (), 0) as Style0
SELECT CONVERT (varchar), GETDATE (), 1) as Style1
SELECT CONVERT (varchar), GETDATE (), 2) as Style2
SELECT CONVERT (varchar), GETDATE (), 3) as Style3
SELECT CONVERT (varchar), GETDATE (), 4) as Style4
SELECT CONVERT (varchar), GETDATE (), 5) as Style5
SELECT CONVERT (varchar), GETDATE (), 6) as Style6
SELECT CONVERT (varchar), GETDATE (), 7) as Style7
SELECT CONVERT (varchar), GETDATE (), 8) as Style8
SELECT CONVERT (varchar), GETDATE (), 9) as Style9
SELECT CONVERT (varchar, GETDATE ()) as Style10
SELECT CONVERT (varchar, GETDATE (), one) as Style11
SELECT CONVERT (varchar), GETDATE (), as Style12
SELECT CONVERT (varchar), GETDATE (), as Style13
SELECT CONVERT (varchar), GETDATE (), as Style14
--select CONVERT (varchar), GETDATE (), as Style15
--select CONVERT (varchar), GETDATE (), as Style16
--select CONVERT (varchar), GETDATE (), as Style17
--select CONVERT (varchar), GETDATE (), () as Style18
--select CONVERT (varchar), GETDATE (), as Style19
SELECT CONVERT (varchar), GETDATE (), as Style21
SELECT CONVERT (varchar), GETDATE (), as Style21
SELECT CONVERT (varchar), GETDATE (), as Style22
SELECT CONVERT (varchar), GETDATE (),) as Style23
SELECT CONVERT (varchar), GETDATE (),) as Style24
SELECT CONVERT (varchar), GETDATE (), as Style25
--select CONVERT (varchar), GETDATE ()) as Style26
--select CONVERT (varchar), GETDATE (), as Style27
--select CONVERT (varchar), GETDATE (), as Style28
--select CONVERT (varchar), GETDATE ()) as Style29
--select CONVERT (varchar), GETDATE (), as Style30
--select CONVERT (varchar), GETDATE (), as Style31
Run Result:
Five, century-digit instance code (the annotation's expression is not a valid style number):
Copy Code code as follows:
SELECT CONVERT (varchar), GETDATE (), as Style100
SELECT CONVERT (varchar), GETDATE (), Style101
SELECT CONVERT (varchar), GETDATE (), 102) as Style102
SELECT CONVERT (varchar), GETDATE (), Style103
SELECT CONVERT (varchar), GETDATE (),) as Style104
SELECT CONVERT (varchar), GETDATE (), as Style105
SELECT CONVERT (varchar), GETDATE (), the) as Style106
SELECT CONVERT (varchar), GETDATE (), Style107
SELECT CONVERT (varchar), GETDATE (), 108) as Style108
SELECT CONVERT (varchar), GETDATE (), 109) as Style109
SELECT CONVERT (varchar), GETDATE (), and as Style110
SELECT CONVERT (varchar, GETDATE (),) as Style111
SELECT CONVERT (varchar), GETDATE (), 112) as Style112
SELECT CONVERT (varchar), GETDATE (), 113) as Style113
SELECT CONVERT (varchar), GETDATE (), 114) as Style114
--select CONVERT (varchar), GETDATE (), as Style115
--select CONVERT (varchar), GETDATE (), 116) as Style116
--select CONVERT (varchar), GETDATE (), 117) as Style117
--select CONVERT (varchar), GETDATE (), 118) as Style118
--select CONVERT (varchar), GETDATE (), 119) as Style119
SELECT CONVERT (varchar), GETDATE (), Style121
SELECT CONVERT (varchar), GETDATE (), 121) as Style121
--select CONVERT (varchar), GETDATE (), 122) as Style122
--select CONVERT (varchar), GETDATE (), 123) as Style123
--select CONVERT (varchar), GETDATE (), 124) as Style124
--select CONVERT (varchar), GETDATE (), Style125
SELECT CONVERT (varchar), GETDATE (), 126) as Style126
SELECT CONVERT (varchar), GETDATE (), 127) as Style127
--select CONVERT (varchar), GETDATE (), 128) as Style128
--select CONVERT (varchar), GETDATE (), 129) as Style129
SELECT CONVERT (varchar), GETDATE (), 130) as Style130
SELECT CONVERT (varchar), GETDATE (), 131) as Style131
Run Result:
SQL converts datetime into a string and intercepts a string
Copy Code code as follows:
Select Sr_child as ' child name ', sr_parents as ' parent name ', Ss_updatetime as ' allotted time ', left (Ss_updatetime,charindex (", Ss_updatetime) -1), SUBSTRING (CONVERT (CHAR), Ss_updatetime,), 1,10) as ' convert format and intercept time ' from Dbo.tb_sell_resources,dbo.tb_sell_ selldetails where sr_id = Ss_rsid and ss_qdstate <> 1 order by Ss_updatetime
String intercept function in sql: SUBSTRING (name,start,end)
Name: field name in string format
Start: Specify starting position (starting value is 1)
End: The position at which the ending of a string is intercepted
SQL Datetime format converted to string format: 2000-01-01 01:01:01 (DateTime) CONVERT (CHAR), Current_timestamp, 120)
Current_timestamp: Current time (where you can write a DateTime format field name, such as Ss_updatetime)
The remaining parameters (CHAR (19), 120, etc.) do not need to be modified
After use 2000-01-01 01:01:01 (Datetime) becomes 2000-01-01 01:01:01 (string format)
