SQL date comparison-convert Conversion Function usage

Format:Convert(Data_type, expression[, Style])Convert(Varchar, field name,Format type)

Note:
This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, Char, varchar)
It is used only when they are converted to each other.

Example:
Select convert (varchar (30), getdate (), 101) now
Result:
Now
---------------------------------------
| 09/15/2001

========================================================== ======================================

The conversion time of a style number is as follows:

Bytes ---------------------------------------------------------------------------------------

Style (2 bits represent the year) | style (4 bits represent the year) | input/output format
Bytes ---------------------------------------------------------------------------------------

0 | 100 | mon dd yyyy hh: miam (or pm)
Bytes ---------------------------------------------------------------------------------------

1 | 101 us | mm/DD/yy
Bytes ---------------------------------------------------------------------------------------

2 | 102 ANSI | YY-mm-dd
Bytes ---------------------------------------------------------------------------------------

3 | 103 English | dd/mm/yy
Bytes ---------------------------------------------------------------------------------------

4 | 104 Germany | dd. mm. yy
Bytes ---------------------------------------------------------------------------------------
5 | 105 Italy | DD-mm-yy
Bytes ---------------------------------------------------------------------------------------
6 | 106 | dd mon YY
Bytes ---------------------------------------------------------------------------------------
7 | 107 | mon DD, YY
Bytes ---------------------------------------------------------------------------------------
Eight | 108 | hh: mm: SS
Bytes ---------------------------------------------------------------------------------------
9 | 109 | mon dd yyyy hh: MI: SS: mmmmam (or pm)
Bytes ---------------------------------------------------------------------------------------
10 | 110 US | mm-dd-yy
Bytes ---------------------------------------------------------------------------------------
11 | 111 Japan | YY/MM/dd
Bytes ---------------------------------------------------------------------------------------
12 | 112 ISO | yymmdd
Bytes ---------------------------------------------------------------------------------------
13 | 113 European default value | dd mon yyyy hh: MI: SS: Mmm (in 24-hour format)
Bytes ---------------------------------------------------------------------------------------
14 | 114 | hh: MI: SS: Mmm (in 24-hour format)
Bytes ---------------------------------------------------------------------------------------
20 | 120 ODBC specifications | yyyy-mm-dd hh: MI: SS (24-hour)
Bytes ---------------------------------------------------------------------------------------
21 | 121 | yyyy-mm-dd hh: MI: SS: Mmm (in 24-hour format)
Bytes ---------------------------------------------------------------------------------------

Statement and query result:
Select convert (varchar (100), getdate (), 0): 05 16 2006 AM
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select convert (varchar (100), getdate (), 6): 16 05 06
Select convert (varchar (100), getdate (), 7): 05 16, 06
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 9): 05 16 2006 10: 57: 46: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select convert (varchar (100), getdate (), 11): 06/05/16
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 13): 16 05 2006 10: 57: 46: 937
Select convert (varchar (100), getdate (), 14): 10: 57: 46: 967
Select convert (varchar (100), getdate (), 20): 10:57:47
Select convert (varchar (100), getdate (), 21): 10:57:47. 157
Select convert (varchar (100), getdate (), 22): 05/16/06 10:57:47 AM
Select convert (varchar (100), getdate (), 23 ):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 AM
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
Select convert (varchar (100), getdate (), 107): 05 16,200 6
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 109): 05 16 2006 10: 57: 49: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 10:57:49
Select convert (varchar (100), getdate (), 121): 2006-

Related Technologies:

Substring (convert (varchar, getdate (), 20 ),):
Select Replace (replace (convert (varchar, getdate (), 120 ),\'-\',\'\'),\'\', \ '), \': \ ', \'): 20040912110608

 

[Important]

By default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many Client ApplicationsProgram(For example, client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

 

When converting from smalldatetime to character data, the style that contains seconds or milliseconds will display zero at these locations. When converting from the datetime or smalldatetime value, you can use the appropriate Char or varchar data type length to cut off the date part that is not required.

The following table shows the style values when converting from float or real to character data.

Value	Output
0 (default)	The maximum value is 6 digits. Use scientific notation as needed.
1	Always 8 bits. Always use scientific notation.
2	Always a 16-bit value. Always use scientific notation.

 

In the following table, the left column indicates the style value when converting from money or smallmoney to character data.

value	output
0 (default)	each of the three digits on the left of the decimal point is not separated by commas. The two digits on the right of the decimal point, for example, 4235.98.
1	each three digits on the left of the decimal point are separated by commas (,). The two digits on the right of the decimal point, for example, 3,510.92.
2	each three digits on the left of the decimal point are not separated by commas. The four digits on the right of the decimal point, for example, 4235.9819.