Shulou(Shulou.com)05/31 Report--

In order to solve the problem of how to convert datetime to string in Sql, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

First, review the syntax format of CONVERT ():

CONVERT ([length], [, style])

2. Here we pay attention to explain the meaning of style:

Style is the conversion style number provided by the SQL Server system when converting DATATIME and SMALLDATETIME data into strings, and different style numbers have different output formats; it is generally used when converting time types (datetime,smalldatetime) and string types (nchar,nvarchar,char,varchar).

3. The following table shows the conversion style numbers provided by the SQL Server system:

-0 or 100 (*) default mon dd yyyy hh:miAM (or PM) 1101 US mm/dd/yyyy2102ANSIyy.mm.dd3103 UK / France dd/mm/yy4104 Germany dd.mm.yy5105 Italian dd-mm-yy6106-dd mon yy7107-mon dd Yy8108-hh:mm:ss-9 or 109 (*) default + millisecond mon dd yyyy hh:mi:ss:mmmAM (or PM) 10110 US mm-dd-yy11111 Japan yy/mm/dd12112ISOyymmdd-13 or 113 (*) European default + millisecond dd mon yyyy hh:mm:ss:mmm (24 h) 14114-hh:mi:ss:mmm (24 h)-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 (* *) ISO8601yyyy-mm-dd Thh:mm:ss.mmm (without 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 states: * default values (style 0 or 100,9 or 109,13 or 113,20 or 120,21 or 121always return century digits (yyyy). * * input when converted to datetime; output when converted to character data. * is dedicated to XML. For conversion from datetime or smalldatetime to character data, the output format is shown in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For the conversion from real to character data, the output is equivalent to style 1. * Hijri is a calendar system with several variations, Microsoft? SQL Server? 2000 uses the Kuwait algorithm.

Fourth, the example code without century digits (the annotation represents an invalid style number):

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 7) AS Style7 SELECT CONVERT (varchar, GETDATE, 8) AS Style8 SELECT CONVERT (varchar, GETDATE, 9) AS Style9 SELECT CONVERT (varchar, GETDATE, 10) AS Style10 SELECT CONVERT (varchar, GETDATE, 11) AS Style11 SELECT CONVERT (varchar, GETDATE, 12) AS Style12 SELECT CONVERT (varchar, GETDATE, 13) AS Style13 SELECT CONVERT (varchar, GETDATE, 14) AS Style14-SELECT CONVERT (varchar (100), GETDATE () 15) AS Style15-SELECT CONVERT (varchar, GETDATE, 16) AS Style16-SELECT CONVERT (varchar, GETDATE, 17) AS Style17-SELECT CONVERT (varchar, GETDATE, 18) AS Style18-SELECT CONVERT (varchar, GETDATE, 19) AS Style19 SELECT CONVERT (varchar, GETDATE, 20) AS Style21 SELECT CONVERT (varchar, GETDATE, 21) AS Style21 SELECT CONVERT (varchar, GETDATE, 22) AS Style22 SELECT CONVERT GETDATE, 23) AS Style23 SELECT CONVERT (varchar, GETDATE, 24) AS Style24 SELECT CONVERT (varchar, GETDATE, 25) AS Style25-SELECT CONVERT (varchar, GETDATE, 26) AS Style26-SELECT CONVERT (varchar, GETDATE, 27) AS Style27-SELECT CONVERT (varchar, GETDATE, 28) AS Style28-SELECT CONVERT (varchar, GETDATE, 29) AS Style29-SELECT CONVERT 30) AS Style30-SELECT CONVERT (varchar (100), GETDATE (), 31) AS Style31

Fifth, the example code with century digits (the annotation represents a non-valid style number):

SELECT CONVERT (varchar), GETDATE (), AS Style100 SELECT CONVERT (varchar), GETDATE (), AS Style101 SELECT CONVERT (varchar), GETDATE (), AS Style102 SELECT CONVERT (varchar), GETDATE (), AS Style103 SELECT CONVERT (varchar), GETDATE (), AS Style104 SELECT CONVERT (varchar), GETDATE (), AS Style105 SELECT CONVERT (varchar), GETDATE (), AS Style106 SELECT CONVERT (varchar (100), GETDATE () AS Style107 SELECT CONVERT (varchar), GETDATE (), AS Style108 SELECT CONVERT (varchar), GETDATE (), AS Style109 SELECT CONVERT (varchar), GETDATE (), AS Style110 SELECT CONVERT (varchar), GETDATE (), AS Style111 SELECT CONVERT (varchar), GETDATE (), AS Style112 SELECT CONVERT (varchar), GETDATE (), AS Style113 SELECT CONVERT (varchar), GETDATE (), AS Style114-SELECT CONVERT (varchar (100), GETDATE () 15) AS Style115-SELECT CONVERT (varchar, GETDATE) AS Style116-SELECT CONVERT (varchar, GETDATE) AS Style117-SELECT CONVERT (varchar, GETDATE, 118,118) AS Style118-SELECT CONVERT (varchar, GETDATE, 119,119) AS Style119 SELECT CONVERT (varchar, GETDATE, AS Style121 SELECT CONVERT AS Style122-SELECT CONVERT (varchar, GETDATE, 12312) AS Style123-SELECT CONVERT (varchar, GETDATE, 12412) AS Style124-SELECT CONVERT (varchar, GETDATE, 125AS Style125 SELECT CONVERT (varchar, GETDATE, 126) AS Style126 SELECT CONVERT (varchar, GETDATE, 127) AS Style127-SELECT CONVERT (varchar, GETDATE, 128AS Style128-SELECT CONVERT ) AS Style129 SELECT CONVERT (varchar (100,130) AS Style130 SELECT CONVERT (varchar (100,131) AS Style131)

Run result: SQL converts datetime to string and intercepts the string to copy the code as follows: select sr_child as' child name', sr_parents as' parent name', ss_updatetime as' allocation time', left (ss_updatetime,CHARINDEX (', ss_updatetime)-1), SUBSTRING (CHAR (19), ss_updatetime, 120) as' conversion format and intercepted time 'from dbo.tb_sell_resources Dbo.tb_sell_selldetails where sr_id = ss_rsid and ss_qdstate 1 order by ss_updatetime

String interception function in sql: SUBSTRING (name,start,end) name: field name in string format start: specify the start position (starting value is 1) end: intercept the position where the string ends in sql convert Datetime format to string format: 2000-01-01 01:01:01 (Datetime) CONVERT (CHAR (19), CURRENT_TIMESTAMP, 120) CURRENT_TIMESTAMP: current time (you can write the field name in Datetime format here For example, ss_updatetime) the remaining parameters (CHAR (19), 120, etc.) need not be modified after 2000-01-01 01:01:01 (Datetime) is changed to 2000-01-01 01:01:01 (string format)

No century digital (yy) with century digital (yyyy) standard input / output * * this is the answer to the question about how to convert datetime into a string in Sql. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.