SQL Date-time format conversion

Source: http://www.cnblogs.com/Gavinzhao/archive/2009/11/10/1599690.html

Use convert in SQL server2000 to get the datetime data type style (full)

Processing of date data formats, two examples:

CONVERT (varchar (16), time one, 20) results: 2007-02-01 08:02/* time is generally a field in the GETDATE () function or data table */

CONVERT (varchar (10), time one, 23) Result: 2007-02-01/*varchar (10) represents the format of the date output, which can occur if not long enough */

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
Using CONVERT:

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

Parameters
Expression

Is any valid microsoft®sql Server™ 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 uses the Kuwaiti algorithm.



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.

Also attached sqlserver2008 each date type value range:

In addition to datetime and smalldatetime, SQL Server 2008 adds four additional time types: Date,time,datetime2 and DateTimeOffset.

Each time type range, precision list:

Data type Time range accuracy

DateTime 1753-01-01 to 9999-12-31 00:00:00 to 23:59:59.997 3.33 ms

smalldatetime 1900-01-01 to 2079-06-06 00:00:00 to 23:59:59 minutes

Date 0001-01-01 to 9999-12-31 days

Time 00:00:00.0000000 to 23:59:59.9999999 100 nanoseconds

DateTime2 0001-01-01 to 9999-12-31 00:00:00 to 23:59:59.9999999 100 nanoseconds

DateTimeOffset 0001-01-01 to 9999-12-31 00:00:00 to 23:59:59.9999999-14:00 to +14:00 100 nanoseconds

List of time-type expressions: Data type output is 12:35:29. 1234567 date 2007-05-08 smalldatetime 2007-05-08 12:35:00 datetime 2007-05-08 12:35:29.123 datetime2 2007-05-08 12:35:29. 1234567 DateTimeOffset 2007-05-08 12:35:29.1234567 +12:15

SQL Date Time format conversion (RPM)