SQL Date-time format conversion

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 Se lect  CONVERT (varchar), GETDATE (), 6): 7 Select CONVERT (varchar),  GETDATE (),):, Selec T CONVERT (varchar), GETDATE (), 8):  10:57:46 Select CONVERT (varchar), GETDATE (), 9): 2006  10:57:4 6:827AM select CONVERT (varchar), GETDATE (),:  05-16-06 Select convert (varchar), GETDATE (), 11): 06/05/ select  CONVERT (varchar), GETDATE (), 060516 Select convert (varchar),  GETDATE (), 13): 16 05 2006 10:57:46:937 select CONVERT (varchar),  GETDATE (): 10:57:46:967 select CONVERT (varchar), GETDATE (), 20 ):  2006-05-16 10:57:47 SelECT CONVERT (varchar), GETDATE (), (+): 2006-05-16  10:57:47.157 Select CONVERT (varchar), GETDATE (), 22): 05/ 16/06 10:57:47  AM Select convert (varchar, GETDATE (), max): 2006-05-16 select  CONVERT (varchar (100), GETDATE (): 10:57:47 select CONVERT (varchar),  GETDATE (), 2006-05-16 10:57:47.250 Select CONVERT ( varchar (+),  GETDATE (), (+): 2006 10:57am Select CONVERT (varchar (+), GETDATE (),  101): 05/16/2006 Sel ECT CONVERT (varchar (103), GETDATE (), 102):  2006.05.16 Select CONVERT (varchar (+), GETDATE (),): 16/05/2006 select  CONVERT (varchar), GETDATE (), 104): 16.05.2006 select  CONVERT (varchar (+), GETDATE (), 105): 16-05-2006 select  CONVERT (varchar), GETDATE (), 106): 2006 select  CONVERT (varchar), GETDATE (), 1 : 2006 select  CONVERT (varchar), GETDATE (), 108): 10:57:49 Select convert (varchar),  GETDATE ( ), 109): 2006 10:57:49:437am Select CONVERT (varcHar (+),  GETDATE (): 05-16-2006 select CONVERT (varchar), GETDATE (), 111):  2006/05/16 Select CONVERT (varchar), GETDATE (), 20060516 select  CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:5 select  CONVERT (varchar), GETDATE (), 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: Use CONVERT:
CONVERT (data_type [(length)], expression [, style])
Parameter 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.
No century digits (yy) with century digits (yyyy) standard input/output * *-0 or 100 (*) default mon dd yyyy hh:miam (or PM) 1 101 US 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 + 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 (*) Europe Default value + Millisecond 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 (without spaces)-130* hijri**** dd m On 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.