Share SQL Date time format conversion _mssql

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

Date data format processing, two examples:

CONVERT (varchar (16), time one, 20) Result: 2007-02-01 08:02/* time is generally getdate () function or field in Datasheet * *

CONVERT (varchar (10), time one, 23) Result: 2007-02-01/*varchar (10) indicates the format of the date output, if not long enough it will occur.

Statement and Query results:

Copy Code code as follows:

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 (MB), 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 (), 130):???? ?????? 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])

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 user-defined data types.
Length

Optional parameters for the nchar, 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 types), or string format styles, whereby float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the data format in the Arabic style using the Kuwaiti algorithm.

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

century Digit (yy) with century digit (yyyy)
Standard
Input/Output * *
-0 or 100 (*) default value mon dd yyyy hh:miam (or PM)
1 101 American Mm/dd/yyyy
2 102 ANSI Yy.mm.dd
3 103 British/French DD/MM/YY
4 104 German Dd.mm.yy
5 105 Italian Dd-mm-yy
6 106-dd Mon yy
7 107-mon DD, yy
8 108-HH:MM:SS
-9 or 109 (*) default + millisecond Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 American Mm-dd-yy
11 111 Japanese Yy/mm/dd
112 ISO YYMMDD
-13 or 113 (*) Euro defaults + 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 Mon yyyy hh:mi:ss:mmmAM
-131* hijri**** Dd/mm/yy Hh:mi:ss:mmmAM

* Default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always return century digits (yyyy).
* * input when converting to datetime, 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 variations, 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, two-digit year 49 is interpreted as 2049, and two-digit year 50 is interpreted as 1950. Many client applications, such as those that are based on OLE automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Cutoff year for two digits") to change the cutoff year used by SQL Server and to treat dates consistently. However, the safest way to do this is to specify a four-digit year.

When you convert from smalldatetime to character data, a style that contains seconds or milliseconds will display zeros in those positions. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.