Conversion of date type data in copy SQL Server to convert

Copy http://www.cnblogs.com/benwu/p/3939044.html

The main description is that SQL Server uses convert to obtain datetime date data in the actual operation process, in the actual operation with the SQL Server database using convert to get datetime date data, the following instance contains a variety of date format conversions.

Statements and Query results:

1. Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
2. Select CONVERT (varchar), GETDATE (), 1): 05/16/0

SQL Server uses convert to get datetime date data, and the following instance contains conversions for various date formats

Statements and Query results:

Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am

Select CONVERT (varchar), GETDATE (), 1): 05/16/06

Select CONVERT (varchar), GETDATE (), 2): 06.05.16

Select CONVERT (varchar), GETDATE (), 3): 16/05/06

Select CONVERT (varchar), GETDATE (), 4): 16.05.06

Select CONVERT (varchar), GETDATE (), 5): 16-05-06

Select CONVERT (varchar), GETDATE (), 6): 16 05 06

Select CONVERT (varchar), GETDATE (), 7): 05 16, 06

Select CONVERT (varchar), GETDATE (), 8): 10:57:46

Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am

Select CONVERT (varchar), GETDATE (), 10): 05-16-06

Select CONVERT (varchar), GETDATE (), 11): 06/05/16

Select CONVERT (varchar), GETDATE (), 12): 060516

Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937

Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967

Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47

Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157

Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM

Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

Select CONVERT (varchar), GETDATE (), 24): 10:57:47

Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250

Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am

Select CONVERT (varchar), GETDATE (), 101): 05/16/2006

Select CONVERT (varchar), GETDATE (), 102): 2006.05.16

Select CONVERT (varchar), GETDATE (), 103): 16/05/2006

Select CONVERT (varchar), GETDATE (), 104): 16.05.2006

Select CONVERT (varchar), GETDATE (), 105): 16-05-2006

Select CONVERT (varchar), GETDATE (), 106): 16 05 2006

Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006

Select CONVERT (varchar), GETDATE (), 108): 10:57:49

Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am

Select CONVERT (varchar), GETDATE (), 110): 05-16-2006

Select CONVERT (varchar), GETDATE (), 111): 2006/05/16

Select CONVERT (varchar), GETDATE (), 112): 20060516

Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513

Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547

Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49

Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700

Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827

Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am

Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

Description

SQL Server uses convert:

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

Parameters

Expression

Is any valid Microsoft? SQL Server? An expression:

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type.

Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports data formats in Arabian styles using the Kuwaiti algorithm.

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

century digits (yy) with century digits (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

4 104 German 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 + MS Mon dd yyyy hh:mi:ss:mmmAM (or PM)

10 110 US Mm-dd-yy

11 111 Japan YY/MM/DD

ISO YYMMDD

-13 or 113 (*) European default + MS DD Mon yyyy hh:mm:ss:mmm (24h)

114-hh:mi:ss:mmm (24h)

-20 or (*) 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 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.

Important by default, SQL Server interprets two-digit years based on the cutoff year 2049. That is, the two-digit year 49 is interpreted as 2049, and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE automation objects, use 2030 as the cutoff year.

SQL Server provides a configuration option ("Two-digit cutoff year") to change the cutoff year used by SQL Server and to make the dates consistent. However, the safest option is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value, you can truncate the unwanted date part by using the appropriate char or varchar data type length.

Conversion of date type data in copy SQL Server to convert