In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data.
在本文中，我们将讨论和学习有关SQL Server数据类型转换操作的基础知识和所有详细信息，还将通过各种示例回顾SQL CONVERT和TRY_CONVERT内置函数。 首先，我们将解释和阐明SQL CONVERT函数的语法，然后我们将学习如何使数据转换过程数字和日期/时间数据类型为字符数据。
隐式与显式 (Implicit vs Explicit)
The process of changing data type of a value into another data type is referred to as data type conversion and also almost all programing languages include some type of data converting functions or functionality. When we turn our perspective to SQL Server in order to discuss details of SQL data converting operations, at first we can separate data conversion process into two parts; implicit and explicit conversions. Implicit conversion is done by SQL Server for internal needs and you can also find additional details in the article Implicit conversion in SQL Server. Explicit conversion is performed explicitly by a database programmer or administrator and at the same time this conversion process is made with help of any data conversion function. In this article, we will particularly focus on the SQL CONVERT function. This function provides a means to convert one data type to another specified data type. At first, we will interpret the syntax of the SQL CONVERT function.
将值的数据类型更改为另一种数据类型的过程称为数据类型转换，而且几乎所有编程语言都包含某种类型的数据转换功能或功能。 为了讨论SQL数据转换操作的细节而将目光转向SQL Server时，首先，我们可以将数据转换过程分为两部分： 隐式和显式转换。 隐式转换由SQL Server根据内部需求完成，您还可以在文章SQL Server中的隐式转换中找到其他详细信息。 显式转换由数据库程序员或管理员显式执行，同时此转换过程借助任何数据转换功能进行。 在本文中，我们将特别关注SQL CONVERT函数。 此功能提供了一种将一种数据类型转换为另一种指定数据类型的方法。 首先，我们将解释SQL CONVERT函数的语法。
句法 (Syntax)
CONVERT (datatype (length), expression, style)
data_type: This parameter defines the target data type which is to be converted. data_type parameter can take these data types as an input which are shown in the below array list.
数据类型： 此参数定义要转换的目标数据类型。 data_type参数可以将这些数据类型作为输入，如下面的数组列表所示。
“bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image”
“ bigint，int，smallint，tinyint，位，十进制，数字，货币，smallmoney，float，实数，datetime，smalldatetime，char，varchar，text，nchar，nvarchar，ntext，binary，varbinary或image”
length: This is an optional parameter which specifies the target data type length. The default value of this parameter is 30.
长度： 这是一个可选参数，用于指定目标数据类型的长度。 该参数的默认值为30。
expression: This parameter specifies the value which we want to convert to another data type.
表达： 此参数指定我们要转换为另一种数据类型的值。
style: This is an integer parameter which specifies the output style of the converted value. This value is more useful to date data type formats.
样式： 这是一个整数参数，用于指定转换后的值的输出样式。 此值对于日期数据类型格式更有用。
Now, we will reinforce this theoretical instructions with various practical samples.
现在，我们将通过各种实际示例来加强这一理论指导。
将Float转换为Int (Convert Float to Int)
In this example, we will convert a float data type to integer. In the following query, we will declare a variable that data type is float and then we will use the SQL CONVERT function in order to convert float value to integer so for that we will perform data converting operation.
在此示例中，我们将浮点数据类型转换为整数。 在下面的查询中，我们将声明一个数据类型为float的变量，然后将使用SQL CONVERT函数将float值转换为整数，以便执行数据转换操作。
--Declaring a float variable
DECLARE @FloatVal AS Float
--Assign a float value to variable
SET @FloatVal = 1132.12345
--Convert float value to int
SELECT CONVERT(int, @FloatVal) AS ConvertedValue
将Float转换为Varchar (Convert Float to Varchar)
In this example, we will convert a float value to varchar value. This example is very similar to previous one but the only difference is that we will convert float value to varchar.
在此示例中，我们将浮点值转换为varchar值。 这个示例与上一个示例非常相似，但是唯一的区别是我们将float值转换为varchar。
--Declaring a float variable
DECLARE @FloatVal AS Float
--Assign a float value to variable
SET @FloatVal = 1132.12345
--Convert float value to varchar
SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue
In the following example, we will not send any value to length parameter of the SQL CONVERT function so length parameter must be set to default value. As already we noted in the syntax explanation of the SQL CONVERT function, this default value is 30 and now we will prove this. In the following query, we will create a temporary table and we will insert converted value to this table and then we will analyze the data structure of this table.
在以下示例中，我们将不发送任何值给length SQL CONVERT函数的参数，因此长度 参数必须设置为默认值。 正如我们在SQL CONVERT函数的语法说明中已经指出的那样，该默认值为30，现在我们将证明这一点。 在以下查询中，我们将创建一个临时表，并将转换后的值插入该表，然后分析该表的数据结构。
DROP TABLE IF EXISTS TestConvertedTable
--Declaring a float variable
DECLARE @FloatVal AS Float
--Assign a float value to variable
SET @FloatVal = 1132.12345
--Convert float value to varchar
SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue INTO TestConvertedTable
--- Analyze the temporary table
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable'
As you can see in the above image, the float data type was converted to varchar value and we did not set the length parameter so SQL Server applied the default value of the length parameter as 30.
如上图所示，float数据类型已转换为varchar值，而我们未设置长度 参数，因此SQL Server应用了长度的默认值 参数为30。
Now, we will set the length parameter of the SQL CONVERT function and then recheck the length of the varchar data type.
现在，我们将设置SQL CONVERT函数的length参数，然后重新检查varchar数据类型的长度。
DROP TABLE IF EXISTS TestConvertedTable
--Declaring a float variable
DECLARE @FloatVal AS Float
--Assign a float value to variable
SET @FloatVal = 1132.12345
--Convert float value to varchar
SELECT CONVERT(varchar(20), @FloatVal) AS ConvertedValue INTO TestConvertedTable
--- Analyze the temporary table
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable'
将钱转换成Varchar (Convert Money to Varchar)
The SQL Server money data type helps to store monetary values. We can define monetary values preceded by a currency symbol however SQL Server does not store the currency symbols or any data which is related to currency symbol. In the following query, we will convert the money data type to varchar and we will also use style parameter of the SQL convert function. With the help of the style parameter, we can determine comma delimiters and length of the right decimal digit. The following chart shows the style parameter value and output of the SQL CONVERT function.
SQL Server货币数据类型有助于存储货币值。 我们可以在货币值之前定义货币符号，但是SQL Server不会存储货币符号或与货币符号相关的任何数据。 在以下查询中，我们将money数据类型转换为varchar，还将使用SQL convert函数的style参数。 借助style参数，我们可以确定逗号分隔符和右十进制数字的长度。 下表显示了样式参数值和SQL CONVERT函数的输出。
Value | Style Parameter | Comma Delimiter | Right decimal digit | Output |
$4936.56 | 0 | no | 2 | 4936.56 |
$4936.56 | 1 | yes | 2 | 4,936.56 |
$4936.56 | 2 | no | 4 | 4936.5600 |
值 | 样式参数 | 逗号分隔符 | 右十进制数字 | 输出量 |
$ 4936.56 | 0 | 没有 | 2 | 4936.56 |
$ 4936.56 | 1个 | 是 | 2 | 4,936.56 |
$ 4936.56 | 2 | 没有 | 4 | 4936.5600 |
DECLARE @Money AS MONEY=$4936.56
SELECT CONVERT(Varchar,@money,0) AS [Style_0] ,
CONVERT(Varchar,@money,1) AS [Style_1] ,
CONVERT(Varchar,@money,2) AS [Style_2]
将浮点和实数转换为Varchar ( Convert Float and Real to Varchar )
Float and real data types are approximate numeric data types in SQL Server and it means that these data types do not store exact values. They store a highly close approximation of the stored value. Now, we will explain the approximate numeric data type conception. In the following query, we will create a table which has two columns and these column’s data types are float and real and then we will insert some numeric values. Actually, we expected two digit numbers in the table however real and float data type stores the closest values to expected values.
浮点和实数数据类型是SQL Server中的近似数字数据类型，这意味着这些数据类型不存储确切的值。 它们存储的值非常接近。 现在，我们将解释近似数值数据类型的概念。 在下面的查询中，我们将创建一个包含两列的表，这些列的数据类型为float和real，然后插入一些数字值。 实际上，我们期望表中有两位数字，但是实数和浮点数据类型存储的值与期望值最接近。
DROP TABLE IF EXISTS TestNumericType
CREATE TABLE TestNumericType (TestValFloat FLOAT,TestValReal REAL)
DECLARE @ValueFloat FLOAT =0.1
DECLARE @ValueReal REAL =0.1
DECLARE @i INT=0
WHILE @i <=1000
BEGIN
SET @ValueFloat=@ValueFloat + 0.1
SET @ValueReal = @ValueReal + 0.1
INSERT INTO TestNumericType (TestValFloat,TestValReal) VALUES(@ValueFloat,@ValueReal)
SET @i = @İ+1
END
SELECT * FROM TestNumericType
The below table is quoted from Microsoft Docs and this table explains the converting style of the float and real data types to textual data.
下表引用了Microsoft Docs ，该表说明了float和real数据类型到文本数据的转换样式。
Tip: Scientific notation is a special method in expressing very large number or small numbers mostly used in science.
提示：科学计数法是一种表达非常多或很少用在科学中的数字的特殊方法。
Value | Output |
0 (default) | A maximum of 6 digits. Used in scientific notation, when appropriate. |
1 | Always 8 digits. Always used in scientific notation. |
2 | Always 16 digits. Always used in scientific notation. |
3 | Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string. |
值 | 输出量 |
0（默认） | 最多6位数字。 适当时以科学计数法使用。 |
1个 | 始终为8位数字。 始终以科学计数法使用。 |
2 | 始终为16位数字。 始终以科学计数法使用。 |
3 | 始终为17位数字。 用于无损转换。 使用这种样式，可以保证每个不同的浮点数或实数值都可以转换为不同的字符串。 |
Now let’s demonstrate an example which includes all data conversion styles according to this table.
现在，让我们演示一个示例，该示例根据此表包括所有数据转换样式。
DECLARE @VarFl float=11234561231231.234
SELECT
CONVERT(varchar(100),@VarFl,0) AS [Style_0],
CONVERT(varchar(100),@VarFl,1) AS [Style_1],
CONVERT(varchar(100),@VarFl,2) AS [Style_2],
CONVERT(varchar(100),@VarFl,3) AS [Style_3],
STR(CONVERT(varchar(100),@VarFl,0),20,16) as [6_Digit],
STR(CONVERT(varchar(100),@VarFl,1),20,16) as [8_Digit],
STR(CONVERT(varchar(100),@VarFl,2),20,16) as [16_Digit],
STR(CONVERT(varchar(100),@VarFl,3),20,16) as [17_Digit]
As you can see in the above, the result set shows that the style parameter changes the output of the SQL CONVERT function.
如上所示，结果集显示style参数更改了SQL CONVERT函数的输出。
将日期/时间转换为Varchar (Convert Date/Time to Varchar)
Date/Time formats can vary according to locale settings and for this reason we require various formats to represent date/time. Imagine that you have an application and different countries users use this application, so if someone connects this application in U.S, you have to represent the date as month/day/year format. On the other side, another user connects this application in U.K. and you have to represent date format as day/month/year format. In this case we have to determine the date/time representing style according to locale date/time setting. sp_helplanguage stored procedure returns supported languages with detailed information’s in SQL Server.
日期/时间格式可能会根据区域设置而有所不同，因此，我们需要各种格式来表示日期/时间。 假设您有一个应用程序，并且不同国家的用户使用该应用程序，因此，如果有人在美国连接此应用程序，则必须将日期表示为月/日/年格式。 另一方面，另一位用户在英国连接了此应用程序，您必须将日期格式表示为日/月/年格式。 在这种情况下，我们必须根据区域设置日期/时间设置来确定日期/时间表示样式。 sp_helplanguage存储过程在SQL Server中返回支持的语言以及详细信息。
In addition, we can determine the language for stated session and in this way we can adapt date/time functions to particular language. In the following query, we will set language to Polish and then we can see the changing of the month and day name according to Polish locale.
此外，我们可以确定指定会话的语言，并以此方式使日期/时间功能适应特定的语言。 在以下查询中，我们将语言设置为波兰语，然后可以根据波兰语区域设置看到月份和日期名称的更改。
SET LANGUAGE 'Polish'
SELECT DATENAME(month, DATEPART(MONTH,GETDATE())) AS [MonthName_Polish]
, DATENAME(WEEKDAY, DATEPART(WEEKDAY,GETDATE())) AS [DayName_Polish]
Tip: As general rule, if you will design a multinational application database, always store the UTC (Coordinated Universal Time) time in one column so that you can avoid date/time problems due to local settings.
提示：通常，如果要设计跨国应用程序数据库，请始终将UTC（世界标准时间）时间存储在一栏中，这样可以避免由于本地设置而引起的日期/时间问题。
The SQL Server CONVERT function offers several options to convert date/time data type to character data and also this character data output can be styled in different standards through the style parameter, such as if we want to convert a GETDATE built-in function result to the German standard, we can use the following query:
SQL Server CONVERT函数提供了几个选项，可以将日期/时间数据类型转换为字符数据，并且也可以通过style参数以不同的标准对字符数据输出进行样式设置，例如是否要将GETDATE内置函数结果转换为德国标准，我们可以使用以下查询：
SELECT GETDATE() AS [NotFormatedDate],CONVERT(Varchar(50),GETDATE(),104) AS [GermanStyleDate]
In the following cheat table, you can find each usage combination of style parameter and whole output format of SQL CONVERT () function for date/time to character data conversions.
在下面的备忘表中，您可以找到样式参数和SQL CONVERT（）函数的整体输出格式的每种用法组合，用于日期/时间到字符数据的转换。
Standard | Style | Output | Century |
Default | 0 | mon dd yyyy hh:miAM/PM | √ |
U.S.A. | 1 | mm/dd/yy |
|
ANSI | 2 | yy.mm.dd |
|
British/French | 3 | dd/mm/yy |
|
German | 4 | dd.mm.yy |
|
Italian | 5 | dd-mm-yy |
|
Shortened month name | 6 | dd mon yy |
|
Shortened month name | 7 | mon dd,yy |
|
24 hour time | 8 | hh:mm:ss |
|
Default + milliseconds | 9 | mon dd yyyy hh:mi:ss:mmmAM/PM | √ |
USA | 10 | mm-dd-yy |
|
JAPAN | 11 | yy/mm/dd |
|
ISO | 12 | yymmdd |
|
Europe default + milliseconds | 13 | dd mon yyyy hh:mi:ss:mmm | √ |
24 hour time with milliseconds | 14 | hh:mi:ss:mmm |
|
ODBC canonical | 20 | yyyy-mm-dd hh:mi:ss | √ |
ODBC canonical (with milliseconds) | 21 | yyyy-mm-dd hh:mi:ss.mmm | √ |
Default | 100 | mon dd yyyy hh:miAM/PM | √ |
U.S. | 101 | mm/dd/yyyy | √ |
ANSI | 102 | yyyy.mm.dd | √ |
British/French | 103 | dd/mm/yyyy | √ |
German | 104 | dd.mm.yyyy | √ |
Italian | 105 | dd-mm-yyyy | √ |
Shortened month name | 106 | dd mon yyyy | √ |
Shortened month name | 107 | mon dd, yyyy | √ |
24 hour time | 108 | hh:mm:ss |
|
Default + milliseconds | 109 | mon dd yyyy hh:mi:ss:mmmAM/PM | √ |
USA | 110 | mm-dd-yyyy | √ |
JAPAN | 111 | yyyy/mm/dd | √ |
ISO | 112 | yyyymmdd | √ |
Europe default + milliseconds | 113 | dd mon yyyy hh:mi:ss:mmm | √ |
24 hour time with milliseconds | 114 | hh:mi:ss:mmm |
|
ODBC canonical | 120 | yyyy-mm-dd hh:mi:ss | √ |
ODBC canonical (with milliseconds) | 121 | yyyy-mm-dd hh:mi:ss.mmm | √ |
ISO8601 | 126 | yyyy-mm-ddThh:mi:ss.mmm | √ |
ISO8601 with time zone Z. | 127 | yyyy-mm-ddThh:mi:ss.mmm | √ |
Hijri | 130 | dd mon yyyy hh:mi:ss:mmmAM/PM | √ (Hijri) |
Hijri | 131 | dd/mm/yy hh:mi:ss:mmmAM/PM | √ (Hijri) |
标准 | 样式 | 输出量 | 世纪 |
默认 | 0 | 星期一dd yyyy hh：miAM / PM | √ |
美国 | 1个 | 毫米/日/年 |
|
美标 | 2 | yy.mm.dd |
|
英国/法国 | 3 | 年/月/日 |
|
德语 | 4 | dd.mm.yy |
|
义大利文 | 5 | dd-mm-yy |
|
缩短的月份名称 | 6 | dd mon yy |
|
缩短的月份名称 | 7 | 周一dd，yy |
|
24小时 | 8 | 时：分：秒 |
|
默认值+毫秒 | 9 | 星期一dd yyyy hh：mi：ss：mmmAM / PM | √ |
| 美国 | 10 | mm-dd-yy |
|
日本 | 11 | yy / mm / dd |
|
ISO标准 | 12 | y |
|
欧洲默认值+毫秒 | 13 | dd mon yyyy hh：mi：ss：mmm | √ |
24小时时间（以毫秒为单位） | 14 | hh：mi：ss：mmm |
|
ODBC规范 | 20 | yyyy-mm-dd hh：mi：ss | √ |
ODBC规范（以毫秒为单位） | 21 | yyyy-mm-dd hh：mi：ss.mmm | √ |
默认 | 100 | 星期一dd yyyy hh：miAM / PM | √ |
我们 | 101 | 毫米/日/年 | √ |
美标 | 102 | yyyy.mm.dd | √ |
英国/法国 | 103 | dd / mm / yyyy | √ |
德语 | 104 | dd.mm.yyyy | √ |
义大利文 | 105 | dd-mm-yyyy | √ |
缩短的月份名称 | 106 | dd mon yyyy | √ |
缩短的月份名称 | 107 | 星期一dd，yyyy | √ |
24小时 | 108 | 时：分：秒 |
|
默认值+毫秒 | 109 | 星期一dd yyyy hh：mi：ss：mmmAM / PM | √ |
美国 | 110 | mm-dd-yyyy | √ |
日本 | 111 | yyyy / mm / dd | √ |
ISO标准 | 112 | yyyymmdd | √ |
欧洲默认值+毫秒 | 113 | dd mon yyyy hh：mi：ss：mmm | √ |
24小时时间（以毫秒为单位） | 114 | hh：mi：ss：mmm |
|
ODBC规范 | 120 | yyyy-mm-dd hh：mi：ss | √ |
ODBC规范（以毫秒为单位） | 121 | yyyy-mm-dd hh：mi：ss.mmm | √ |
ISO8601 | 126 | yyyy-mm-ddThh：mi：ss.mmm | √ |
具有时区Z的ISO8601。 | 127 | yyyy-mm-ddThh：mi：ss.mmm | √ |
ij | 130 | dd mon yyyy hh：mi：ss：mmmAM / PM | √ （回历） |
ij | 131 | dd / mm / yy hh：mi：ss：mmmAM / PM | √ （回历） |
ow, we will demonstrate this cheat table with all combinations for the SQL GETDATE function so that we can figure out the influence of the style parameter to the SQL CONVERT function outputs.
现在，我们将使用SQL GETDATE函数的所有组合来演示该备忘表，以便我们可以确定样式参数对SQL CONVERT函数输出的影响。
SELECT 'Default ' AS [Standart] ,'0' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),0) AS [ConvertedFormat]
UNION ALL
SELECT 'U.S.A.' AS [Standart] ,'1' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),1) AS [ConvertedFormat]
UNION ALL
SELECT 'ANSI' AS [Standart] ,'2' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),2) AS [ConvertedFormat]
UNION ALL
SELECT 'British/French' AS [Standart] ,'3' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),3) AS [ConvertedFormat]
UNION ALL
SELECT 'German' AS [Standart] ,'4' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),4) AS [ConvertedFormat]
UNION ALL
SELECT 'Italian' AS [Standart] ,'5' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),5) AS [ConvertedFormat]
UNION ALL
SELECT 'Shortened month name' AS [Standart] ,'6' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),6) AS [ConvertedFormat]
UNION ALL
SELECT 'Shortened month name' AS [Standart] ,'7' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),7) AS [ConvertedFormat]
UNION ALL
SELECT '24 hour time' AS [Standart] ,'8' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),8) AS [ConvertedFormat]
UNION ALL
SELECT 'Default + milliseconds' AS [Standart] ,'9' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),9) AS [ConvertedFormat]
UNION ALL
SELECT 'USA' AS [Standart] ,'10' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),10) AS [ConvertedFormat]
UNION ALL
SELECT 'JAPAN' AS [Standart] ,'11' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),11) AS [ConvertedFormat]
UNION ALL
SELECT 'ISO' AS [Standart] ,'12' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),12) AS [ConvertedFormat]
UNION ALL
SELECT 'Europe default + milliseconds' AS [Standart] ,'13' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),13) AS [ConvertedFormat]
UNION ALL
SELECT ' 24 hour time with milliseconds' AS [Standart] ,'14' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),14) AS [ConvertedFormat]
UNION ALL
SELECT 'ODBC canonical' AS [Standart] ,'20' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),20) AS [ConvertedFormat]
UNION ALL
SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'21' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),21) AS [ConvertedFormat]
UNION ALL
SELECT 'Default ' AS [Standart] ,'100' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),100) AS [ConvertedFormat]
UNION ALL
SELECT 'U.S.' AS [Standart] ,'101' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),101) AS [ConvertedFormat]
UNION ALL
SELECT 'ANSI' AS [Standart] ,'102' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),102) AS [ConvertedFormat]
UNION ALL
SELECT 'British/French' AS [Standart] ,'103' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),103) AS [ConvertedFormat]
UNION ALL
SELECT 'German' AS [Standart] ,'104' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),104) AS [ConvertedFormat]
UNION ALL
SELECT 'Italian' AS [Standart] ,'105' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),105) AS [ConvertedFormat]
UNION ALL
SELECT 'Shortened month name' AS [Standart] ,'106' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),106) AS [ConvertedFormat]
UNION ALL
SELECT 'Shortened month name' AS [Standart] ,'107' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),107) AS [ConvertedFormat]
UNION ALL
SELECT ' 24 hour time' AS [Standart] ,'108' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),108) AS [ConvertedFormat]
UNION ALL
SELECT 'Default + milliseconds' AS [Standart] ,'109' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),109) AS [ConvertedFormat]
UNION ALL
SELECT 'USA' AS [Standart] ,'110' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),110) AS [ConvertedFormat]
UNION ALL
SELECT 'JAPAN' AS [Standart] ,'111' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),111) AS [ConvertedFormat]
UNION ALL
SELECT 'ISO' AS [Standart] ,'112' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),112) AS [ConvertedFormat]
UNION ALL
SELECT 'Europe default + milliseconds' AS [Standart] ,'113' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),113) AS [ConvertedFormat]
UNION ALL
SELECT ' 24 hour time with milliseconds' AS [Standart] ,'114' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),114) AS [ConvertedFormat]
UNION ALL
SELECT 'ODBC canonical' AS [Standart] ,'120' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),120) AS [ConvertedFormat]
UNION ALL
SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'121' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),121) AS [ConvertedFormat]
UNION ALL
SELECT 'ISO8601' AS [Standart] ,'126' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),126) AS [ConvertedFormat]
UNION ALL
SELECT 'ISO8601 with time zone Z.' AS [Standart] ,'127' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),127) AS [ConvertedFormat]
UNION ALL
SELECT 'Hijri' AS [Standart] ,'130' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),130) AS [ConvertedFormat]
UNION ALL
SELECT 'Hijri' AS [Standart] ,'131' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),131) AS [ConvertedFormat]
Now, we will mention some points which we have to consider about date/time conversion operation. At the beginning of this section, we talked about SQL Server language options and settings, so if we change the language setting it affects some of the date/time conversion style outputs. In the bellow example, we want to convert results from the GETDATE function so this setting directly affects the abbreviated month name of the SQL CONVERT function.
现在，我们将提及一些有关日期/时间转换操作的要点。 在本节的开头，我们讨论了SQL Server语言选项和设置，因此，如果更改语言设置，它将影响某些日期/时间转换样式的输出。 在下面的示例中，我们要转换GETDATE函数的结果，因此此设置直接影响SQL CONVERT函数的缩写月份名称。
SET LANGUAGE lietuvių;
SELECT DATEADD(YEAR,-100,GETDATE()) AS [Year_1919]
,CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate]
SET LANGUAGE Croatian;
SELECT
DATEADD(YEAR,100,GETDATE()) AS [Year_2119],
CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate]
Another consideration is about year format. In some circumstances, we can use style parameter which does not return century format but this case may cause some confusing situations. Such as in the following sample we have two different dates and also these dates store different years however the output of the SQL CONVERT function are similar.
另一个考虑因素是年份格式。 在某些情况下，我们可以使用不返回世纪格式的样式参数，但这种情况可能会引起一些混乱的情况。 如下面的示例中，我们有两个不同的日期，这些日期也存储了不同的年份，但是SQL CONVERT函数的输出是相似的。
SELECT
DATEADD(YEAR,-100,GETDATE()) AS [Year_1919]
,CONVERT(varchar(100),DATEADD(YEAR,-100,GETDATE()),1) AS [ConvertedDate]
SELECT
DATEADD(YEAR,100,GETDATE()) AS [Year_2119],
CONVERT(varchar(100),DATEADD(YEAR,100,GETDATE()),1) AS [ConvertedDate]
TRY_CONVERT（） (TRY_CONVERT ())
The SQL TRY_CONVERT function is an advanced form of the SQL CONVERT function. The main advantage of the SQL TRY_CONVERT function is protecting from data converting errors during query execution. It is possible that we can experience errors on data convert process with the SQL COVERT operation due to non-suitable or dirty data. However, the SQL TRY_CONVERT function allows us to avoid these types of errors. At the same time, there are no syntax differences between the SQL CONVERT and TRY_CONVERT functions. The SQL TRY_CONVERT function returns NULL value if the data conversion generate an error. In the below example, at first we will try to convert character value to an integer through the SQL CONVERT function and this operation will return an error.
SQL TRY_CONVERT函数是SQL CONVERT函数的高级形式。 SQL TRY_CONVERT函数的主要优点是可以防止查询执行期间发生数据转换错误。 由于数据不合适或不干净，我们可能会在使用SQL COVERT操作的数据转换过程中遇到错误。 但是，SQL TRY_CONVERT函数使我们能够避免这些类型的错误。 同时，SQL CONVERT和TRY_CONVERT函数之间没有语法差异。 如果数据转换产生错误，SQL TRY_CONVERT函数将返回NULL值。 在下面的示例中，首先，我们将尝试通过SQL CONVERT函数将字符值转换为整数，并且该操作将返回错误。
SELECT CONVERT(INT,'AnyString') AS ConvertFunc
On the other hand, if we use the SQL TRY_CONVERT function instead of the SQL CONVERT function for the same query it will return NULL value.
另一方面，如果我们对同一查询使用SQL TRY_CONVERT函数而不是SQL CONVERT函数，它将返回NULL值。
SELECT TRY_CONVERT(INT,'AnyString') AS ConvertFunc
结论 (Conclusion)
In this article, we thoroughly reviewed the SQL CONVERT function syntax, details and significant considerations. Data conversion processes have wide usage, in practice, therefore in this article we demonstrated various examples. I am recommend that you be careful with date/time data type conversion if your database is being used in different countries. Likewise formatting to date/time data types is another important point.
在本文中，我们彻底回顾了SQL CONVERT函数的语法，详细信息和重要注意事项。 数据转换过程在实践中用途广泛，因此在本文中，我们演示了各种示例。 如果在不同国家/地区使用数据库，建议您在进行日期/时间数据类型转换时要小心。 同样，格式化为日期/时间数据类型是另一个重要点。
2459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
