Shulou(Shulou.com)06/01 Report--

Use CONVERT:

CONVERT (data_type [(length)], expression [, style])

Convert (varchar (10), field name, translation format)

CONVERT (nvarchar (10), count_time,121)

CONVERT is a date conversion function, usually in time type (datetime,smalldatetime) and string type (nchar,nvarchar,char,varchar)

The three parameters of the function used when converting each other, the first parameter is the size after conversion, the second is the field or function of the conversion date, and the third is the format of the conversion.

Parameters.

Expression

Is any valid Microsoft ®SQL Server ™expression.

Data_type

The data types provided by the target system, including bigint and sql_variant. User-defined data types cannot be used.

Length

Optional parameters of the nchar, nvarchar, char, varchar, binary, or varbinary data type.

Style

Date format style to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports data formats in the Arabic style using the Kuwaiti algorithm.

In the table, the two columns on the left represent style values that convert datetime or smalldatetime to character data. Add 100 to the style value to get a four-digit year (yyyy) including century digits.

Without century digital (yy) with century digital (yyyy)

Standard

Input / output * *

0 or 100 (*) default value mon dd yyyy hh:miAM (or PM)

1 101 US mm/dd/yyyy

2 102 ANSI yy.mm.dd

3 103 UK / France dd/mm/yy

4104 German dd.mm.yy

5 105 Italy dd-mm-yy

6106-dd mon yy

7 107-mon dd, yy

8 108-hh:mm:ss9 or 109 (*) default + millisecond mon dd yyyy hh:mi:ss:mmmAM (or PM)

10 110 US mm-dd-yy

11 111 Japan yy/mm/dd

12112 ISO yymmdd13 or 113 (*) European default + millisecond dd mon yyyy hh:mm:ss:mmm (24 h)

14 114-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 (* *) ISO8601 yyyy-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 default value (style 0 or 100,9 or 109,13 or 113,20 or 120,21 or 121is always returned to century digits (yyyy).

* * input when converted to datetime; output when converted to character data.

* 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, and Microsoft ®SQL Server ™2000 uses the Kuwait algorithm.

Important by default, SQL Server interprets a two-digit year based on the cut-off year 2049. That is, a two-digit year 49 is interpreted as 2049, while a two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE automation objects, use 2030 as the cut-off year. SQL Server provides a configuration option ("two-digit cutoff year") to change the cutoff year used by SQL Server and to handle date consistency. The safest way, however, is to specify a four-digit year.

When converting from smalldatetime to character data, styles that contain seconds or milliseconds display zeros at these locations. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.

Statement and query results:

Select CONVERT (varchar, GETDATE (), 0): 05 16 2006 10:57AMSelect CONVERT (varchar, GETDATE (), 1): 05/16/06Select CONVERT (varchar, GETDATE (), 2): 06.05.16Select CONVERT (varchar, GETDATE (), 3): 16/05/06Select CONVERT (varchar, GETDATE, 4): 16.05.06Select CONVERT (varchar, GETDATE (), 5): 16-05-06Select CONVERT (varchar (100), GETDATE () 6): 16 05 06Select CONVERT (varchar, GETDATE (), 7): 0516, 06Select CONVERT (varchar, GETDATE (), 8): 10:57:46Select CONVERT (varchar, GETDATE (), 9): 0516 2006 10:57:46:827AMSelect CONVERT (varchar, GETDATE (), 10): 05-16-06Select CONVERT (varchar, GETDATE (), 11): 06/05/16Select CONVERT (varchar, GETDATE (), 12): 060516Select CONVERT (varchar (100), GETDATE () 13): 16 05 2006 10:57:46:937Select CONVERT (varchar, GETDATE (), 14): 10:57:46:967Select CONVERT (varchar (100), GETDATE (), 20): 2006-05-16 10:57:47Select CONVERT (varchar (100), GETDATE (), 21): 2006-05-16 10:57:47.157Select CONVERT (varchar, GETDATE (), 22): 05lap 1606 10:57:47 AMSelect CONVERT (varchar (100), GETDATE () 23): 2006-05-16Select CONVERT (varchar, GETDATE (), 24): 10:57:47Select CONVERT (varchar, GETDATE (), 25): 2006-05-16 10:57:47.250Select CONVERT (varchar, GETDATE (), 100): 05 16 2006 10:57AMSelect CONVERT (varchar, GETDATE): 05/16/2006Select CONVERT (varchar, GETDATE): 2006.05.16Select CONVERT (varchar (100), GETDATE () 16/05/2006Select CONVERT: 16.05.2006Select CONVERT (varchar, GETDATE): 16-05-2006Select CONVERT (varchar, GETDATE): 16 05 2006Select CONVERT (varchar, GETDATE): 05 16, 2006Select CONVERT (varchar, GETDATE): 10:57:49Select CONVERT (varchar, GETDATE, 109): 05 16 2006 10:57:49:437AMSelect CONVERT (varchar GETDATE: 05-16-2006Select CONVERT: 2006/05/16Select CONVERT (varchar, GETDATE): 20060516Select CONVERT (varchar, GETDATE): 16 05 2006 10:57:49:513Select CONVERT (varchar, GETDATE): 10:57:49:547Select CONVERT: varchar, GETDATE (): 2006-05-16 10:57:49Select CONVERT (varchar, GETDATE) 2006-05-16 10:57:49.700Select CONVERT (varchar, GETDATE): 2006-05-16T10:57:49.827Select CONVERT (varchar, GETDATE, 1330): 18?

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.