SQL Server Time Conversion

use convert in SQL server2000 to get the datetime data type style (full) Date data format processing, two examples: convert (varchar ( -), Time one, -Results -- Geneva- on  ,: Geneva/*time is generally a field in the GETDATE () function or data table*/CONVERT (varchar (Ten), Time one, atResults -- Geneva- on /*varchar (10) represents the format of the date output, which can occur if not long enough to intercept*/statements and Query results: Select CONVERT (varchar ( -), GETDATE (),0): to  - 2006 Ten: 57AMSelect CONVERT (varchar ( -), GETDATE (),1): to/ -/ .Select CONVERT (varchar ( -), GETDATE (),2):06.05. -Select CONVERT (varchar ( -), GETDATE (),3): -/ to/ .Select CONVERT (varchar ( -), GETDATE (),4):16.05. .Select CONVERT (varchar ( -), GETDATE (),5): -- to- .Select CONVERT (varchar ( -), GETDATE (),6): -  to  .Select CONVERT (varchar ( -), GETDATE (),7): to  -, .Select CONVERT (varchar ( -), GETDATE (),8):Ten: $: $Select CONVERT (varchar ( -), GETDATE (),9): to  - 2006 Ten: $: $: 827AMSelect CONVERT (varchar ( -), GETDATE (),Ten): to- -- .Select CONVERT (varchar ( -), GETDATE (), One): ./ to/ -Select CONVERT (varchar ( -), GETDATE (), A):060516Select CONVERT (varchar ( -), GETDATE (), -): -  to 2006 Ten: $: $:937Select CONVERT (varchar ( -), GETDATE (), -):Ten: $: $:967Select CONVERT (varchar ( -), GETDATE (), -):2006- to- - Ten: $: -Select CONVERT (varchar ( -), GETDATE (), +):2006- to- - Ten: $:47.157Select CONVERT (varchar ( -), GETDATE (), A): to/ -/ . Ten: $: -amselect CONVERT (varchar ( -), GETDATE (), at):2006- to- -Select CONVERT (varchar ( -), GETDATE (), -):Ten: $: -Select CONVERT (varchar ( -), GETDATE (), -):2006- to- - Ten: $:47.250Select CONVERT (varchar ( -), GETDATE (), -): to  - 2006 Ten: 57AMSelect CONVERT (varchar ( -), GETDATE (),101): to/ -/2006Select CONVERT (varchar ( -), GETDATE (),102):2006.05. -Select CONVERT (varchar ( -), GETDATE (),103): -/ to/2006Select CONVERT (varchar ( -), GETDATE (),104):16.05.2006Select CONVERT (varchar ( -), GETDATE (), the): -- to-2006Select CONVERT (varchar ( -), GETDATE (),106): -  to 2006Select CONVERT (varchar ( -), GETDATE (),107): to  -,2006Select CONVERT (varchar ( -), GETDATE (),108):Ten: $: theSelect CONVERT (varchar ( -), GETDATE (),109): to  - 2006 Ten: $: the: 437AMSelect CONVERT (varchar ( -), GETDATE (), the): to- --2006Select CONVERT (varchar ( -), GETDATE (),111):2006/ to/ -Select CONVERT (varchar ( -), GETDATE (), the):20060516Select CONVERT (varchar ( -), GETDATE (),113): -  to 2006 Ten: $: the:513Select CONVERT (varchar ( -), GETDATE (), the):Ten: $: the:547Select CONVERT (varchar ( -), GETDATE (), -):2006- to- - Ten: $: theSelect CONVERT (varchar ( -), GETDATE (),121):2006- to- - Ten: $:49.700Select CONVERT (varchar ( -), GETDATE (),126):2006- to-16t10: $:49.827Select CONVERT (varchar ( -), GETDATE (), the): -???? ??????1427 Ten: $: the: 907AMSelect CONVERT (varchar ( -), GETDATE (),131): -/Geneva/1427 Ten: $: the: 920AM Description: Use Convert:convert (data_type [(length)], expression [, style]) parameter expression is any valid Microsoft®sql Se Rver™ expression: Data_type the data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. Lengthnchar, nvarchar,Char, varchar, binary, or varbinary optional parameters for the data type. Style date format, whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar,Char, varchar, nchar, or nvarchar data type), or a string format style tofloat, real, money, or smallmoney data into character data (nchar, nvarchar,Char, varchar, nchar, or nvarchar data type). 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. Adds a style value to the -, the four-bit year (yyyy), which includes century digits, is available. century Digit (yy) with century digit (yyyy) standard input/Output * *-0Or -(*Default value Mon dd yyyy hh:miam (or PM)1 101United States mm/dd/yyyy2 102ANSI yy.mm.dd3 103UK/France dd/mm/yy4 104dd.mm.yy Germany5  thedd-mm-, Italyyy6 106-dd Mon yy7 107-Mon dd, yy8 108-Hh:mm:ss-9Or109(*) Default value +millisecond Mon dd yyyy hh:mi:ss:mmmAM (or PM)Ten  theUnited States mm-dd-yy One 111Japan yy/mm/DD A  theISO YYMMDD- -Or113(*) European default value +MS DD Mon yyyy hh:mm:ss:mmm (24h) -  the-hh:mi:ss:mmm (24h)- -Or -(*) ODBC Specification yyyy-mm-DD Hh:mm:ss[.fff]- +Or121(*) ODBC specification (with milliseconds) yyyy-mm-DD Hh:mm:ss[.fff]-126(***) ISO8601 yyyy-mm-DD Thh:mm:ss.mmm (with no spaces)- the* hijri****dd Mon yyyy hh:mi:ss:mmmAM-131* hijri**** dd/mm/yy Hh:mi:ss:mmmAM* Default value (style0Or -、9Or109、 -Or113、 -Or -、 +Or121) always returns century digits (yyyy). **input when converted to datetime, or output when converted to character data. specifically for XML. For conversions from DateTime or smalldatetime to character data, the output format is shown in the table. For thefloat, money, or smallmoney to character data, output is equivalent to style2。 For conversions from Real to character data, the output is equivalent to the style1. Hijri is a calendar system with several forms of change, Microsoft®sql Server™ -the Kuwaiti algorithm used therein. Important by default, SQL Server is based on the cutoff year2049Explains the two-digit year. That is, a two-digit year thebe interpreted as2049, whereas a two-digit year -be interpreted as1950。 Many client applications, such as those based on OLE Automation objects, use2030As 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 use the appropriateCharor varchar data type length to truncate the unwanted date part.

SQL Server Time Conversion