Detailed explanation of CONVERT function in SQL

The following will introduce you to the CONVERT function in SQL. The CONVERT function is a function used for data conversion in SQL. If you are interested in this, you may wish to take a look. I believe it will be helpful for you to learn SQL.

Use CONVERT:

CONVERT (data_type[(length)], expression [, style])

The parameter

expression

is any valid Microsoft® SQL Server™ expression.

data_type

Data types provided by the target system, including bigint and sql_variant. User-defined data types cannot be used.

length

Optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data type.

style

Date format style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar or nvarchar data type); or string format style, which converts float, real, money or smallmoney Data is converted to character data (nchar, nvarchar, char, varchar, nchar or nvarchar data types).

SQL Server supports data formats in Arabic style using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values ​​that convert datetime or smalldatetime into character data. Add 100 to the style value to obtain a four-digit year (yyyy) including the century digit.

* The default value (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns the century digit (yyyy).
** Input when converted to datetime; output when converted to character data.
*** Specifically for XML. For conversion from datetime or smalldatetime to character data, the output format is as 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.

Important By default, SQL Server interprets two-digit years based on the cutoff year 2049. That is, a two-digit year 49 is interpreted as 2049, and a two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cut-off year") that allows you to change the cut-off year used by SQL Server and treat dates consistently. The safest approach, however, is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros in these positions. When converting from datetime or smalldatetime values, you can truncate unwanted date parts by using the appropriate char or varchar data type length.

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

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

Return Type

Returns the same value as data type 0.