Method for converting a datetime to a string in SQL (convert) _mssql

Source: Internet
Author: User
Tags getdate string format
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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.