SQL Date-time format conversion

Use convert in SQL server2000 to get processing of datetime data type style (full) Date data formats, two examples: CONVERT (varchar (16), time one, 20) results: 2007-02-01 08:02/* Time is generally getdate () function or field in the data table */convert (varchar (10), time one, 23) Result: 2007-02-01/*varchar (10) represents the format of the date output, if not long enough it will take place */ Statement and query Result: Select CONVERT (varchar), GETDATE (), 0): 2006 10:57amselect CONVERT (varchar), GETDATE (), 1): 05/16/0 6Select 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): 06Select CONVERT (varchar (+), GETDATE (), 7): 06Select convert (varchar), GETDATE (8): 10 : 57:46select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827amselect CONVERT (varchar), GETDATE (), 10): 0 5-16-06select CONVERT (varchar), GETDATE (), each): 06/05/16select convert (varchar), GETDATE (), 060516Select CONVERT (varchar), GETDATE (): 2006 10:57:46:937seleCT CONVERT (varchar), GETDATE (), +): 10:57:46:967select convert (varchar), GETDATE (), 20): 2006-05-16 10:57:47select CONVERT (varchar), GETDATE (), +): 2006-05-16 10:57:47.157select convert (varchar), GETDATE (), 22 ): 05/16/06 10:57:47 amselect CONVERT (varchar), GETDATE (), max): 2006-05-16select CONVERT (varchar), GETDATE (), 24 ): 10:57:47select CONVERT (varchar), GETDATE (), (+): 2006-05-16 10:57:47.250select convert (varchar), GETDATE (), : 2006 10:57amselect CONVERT (varchar (1), GETDATE (), 101): 05/16/2006select CONVERT (varchar), GETDATE (), : 2006.05.16Select CONVERT (varchar), GETDATE (), 103): 16/05/2006select convert (varchar), GETDATE (), 104): 16.05.2006Select CONVERT (varchar), GETDATE (), max.: 16-05-2006select convert (varchar), GETDATE (), 106): 16 05 2006Select CONVERT (varchar), GETDATE (), 107): 2006Select convert (varchar, GETDATE (), 108): 10:57:49select CONVERT (varchar), GETDATE (), 109): 05 16 2006 10:57:49:437amselect CONVERT (varchar), GETDATE (), (a): 05-16-2006select convert (varchar), GETDATE (), 111): 2006/05/ 16Select CONVERT (varchar), GETDATE (), 113): 20060516Select convert (varchar), GETDATE (), 16 05 2006 10:57:49 : 513Select CONVERT (varchar), GETDATE (), 10:57:49:547select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700select convert (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907amselect CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am Description: Using Convert:convert (Data_ Type [(length)], expression [, style]) argument 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. Optional parameters for Lengthnchar, nvarchar, char, varchar, binary, or varbinary data types.  style 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 converts float, real, money, or smallmoney data 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&NB SP;2 102 ANSI yy.mm.dd 3 103 UK/France DD/MM/YY 4 104 Germany dd.mm.yy 5 105 Italy dd-mm-yy 6 106-dd Mon yy&nbs P;7 107-mon DD, yy 8 108-hh:mm:ss -9 or 109 (*) default + MS Mon dd yyyy hh:mi:ss:mmmAM (or PM)  10 110 US mm- DD-YY 11 111 Japan yy/mm/dd 12 ISO yymmdd -13 or 113 (*) European default + MS DD Mon yyyy hh:mm:ss:mmm (24h)  14 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& nbsp;-131* hijri**** dd/mmThe/yy hh:mi:ss:mmmam * 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.