T-SQL time format CONVERT (), t-sqlconvert
Function
Returns the expression converted to the provided data type.
Syntax
CONVERT (data type, expression [, format-style])
Example:
convert(varchar(10),getdate(),112)
Parameters
The data type expression is converted to the data type.
The expression to be converted.
Format-style is the style code that describes the date format string to be used for converting the string to the date or time data type and the opposite conversion process. The value of the format-style parameter has the following meanings: Century (yy) and Century (yyyy) Output
Note:
By default, SQL Server interprets two-digit years based on the year ending on January 1, 2049. In other words, the two-digit year 49 is interpreted as 2049, and the two-digit year 50 is interpreted as 1950. Many client applications (such as automated object-based applications) are used as of January 1, 2030. SQL Server provides the two digit year cutoff configuration option to change the end year used by SQL Server for consistent date processing. We recommend that you specify a four-digit year.
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)
