SQL SERVER Date format conversion detailed _mysql

How SQL SERVER 2000 obtains the current system time with SQL statements
is to use GETDATE ();

getdate in SQL () January 08, 2008 Tuesday 14:59
A very powerful date format function in SQL Server

Copy Code code as follows:

Select CONVERT (varchar), GETDATE (), 0);-2008 10:57am
Select CONVERT (varchar), GETDATE (), 1);--05/16/08
Select CONVERT (varchar), GETDATE (), 2);--08.05.16
Select CONVERT (varchar), GETDATE (), 3);--16/05/08
Select CONVERT (varchar), GETDATE (), 4);--16.05.08
Select CONVERT (varchar), GETDATE (), 5);--16-05-08
Select CONVERT (varchar), GETDATE (), 6);--16 05 08
Select CONVERT (varchar), GETDATE (), 7);--05 16, 08
Select CONVERT (varchar), GETDATE (), 8);-10:57:46
Select CONVERT (varchar), GETDATE (), 9);-2008 10:57:46:827am
Select CONVERT (varchar), GETDATE (), 10);--05-16-08
Select CONVERT (varchar), GETDATE (), 11);--08/05/16
Select CONVERT (varchar), GETDATE (), 12);--080516
Select CONVERT (varchar), GETDATE (), 13)--16 05 2008 10:57:46:937
Select CONVERT (varchar), GETDATE (), 14);--10:57:46:967
Select CONVERT (varchar), GETDATE (), 20;--2008-05-16 10:57:47
Select CONVERT (varchar), GETDATE (), 21);--2008-05-16 10:57:47.157
Select CONVERT (varchar), GETDATE ();--05/16/08 10:57:47 AM
Select CONVERT (varchar), GETDATE (), 23);--2008-05-16
Select CONVERT (varchar), GETDATE (), 24);-10:57:47
Select CONVERT (varchar), GETDATE (), 25);--2008-05-16 10:57:47.250
Select CONVERT (varchar), GETDATE (), 2008 10:57am
Select CONVERT (varchar), GETDATE (), 101);--05/16/2008
Select CONVERT (varchar), GETDATE (), 102);--2008.05.16
Select CONVERT (varchar), GETDATE (), 103);--16/05/2008
Select CONVERT (varchar), GETDATE (), 104);--16.05.2008
Select CONVERT (varchar), GETDATE (), 105);--16-05-2008
Select CONVERT (varchar), GETDATE (), 106);--16 05 2008
Select CONVERT (varchar), GETDATE (), 107);--05 16, 2008
Select CONVERT (varchar), GETDATE (), 108);-10:57:49
Select CONVERT (varchar), GETDATE (), 109);--2008 10:57:49:437am
Select CONVERT (varchar), GETDATE (), 110);--05-16-2008
Select CONVERT (varchar), GETDATE (), 111);--2008/05/16
Select CONVERT (varchar), GETDATE (), 112);--20080516
Select CONVERT (varchar), GETDATE (), 113)--16 05 2008 10:57:49:513
Select CONVERT (varchar), GETDATE (), 114);--10:57:49:547
Select CONVERT (varchar), GETDATE (), 120;--2008-05-16 10:57:49
Select CONVERT (varchar), GETDATE (), 121);--2008-05-16 10:57:49.700
Select CONVERT (varchar), GETDATE (), 126);--2008-05-16t10:57:49.827
Select CONVERT (varchar), GETDATE (), 130);--???? ?????? 1427 10:57:49:907am

Description:
Use CONVERT:

Copy Code code as follows:

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.

Copy Code code as follows:

Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am