https://www.sqlshack.com/sql-convert-function/

在本文中，我们将讨论和学习有关SQL Server数据类型转换操作的基础知识和所有详细信息，还将通过各种示例回顾SQL CONVERT和TRY_CONVERT内置函数。首先，我们将解释和阐明SQL CONVERT函数的语法，然后我们将学习如何使数据转换过程数字和日期/时间数据类型为字符数据。

隐式与显式

将值的数据类型更改为另一种数据类型的过程称为数据类型转换，而且几乎所有编程语言都包含某种类型的数据转换功能或功能。为了讨论SQL数据转换操作的细节而将目光转向SQL Server时，首先，我们可以将数据转换过程分为两部分：隐式和显式转换。隐式转换由SQL Server完成，以满足内部需求，您还可以在文章SQL Server中的隐式转换中找到其他详细信息。。显式转换由数据库程序员或管理员显式执行，同时此转换过程借助任何数据转换功能进行。在本文中，我们将特别关注SQL CONVERT函数。此功能提供了一种将一种数据类型转换为另一种指定数据类型的方法。首先，我们将解释SQL CONVERT函数的语法。

语法

CONVERT (datatype (length), expression, style)

  • data_type： 此参数定义要转换的目标数据类型。data_type参数可以将这些数据类型作为输入，如下面的数组列表所示。“ bigint，int，smallint，tinyint，bit，decimal，numeric，money，smallmoney，float，real，datetime，smalldatetime，char，varchar，text，nchar，nvarchar，ntext，binary，varbinary或image”
  • length： 这是一个可选参数，用于指定目标数据类型的长度。该参数的默认值为30。
  • expression： 此参数指定我们要转换为另一种数据类型的值。
  • style： 这是一个整数参数，用于指定转换后的值的输出样式。此值对于日期数据类型格式更有用。

现在，我们将通过各种实际示例来加强这一理论指导。

将Float转换为Int

在此示例中，我们将浮点数据类型转换为整数。在以下查询中，我们将声明一个数据类型为float的变量，然后将使用SQL CONVERT函数将float值转换为整数，以便执行数据转换操作。

  1. --Declaring a float variable
  2. DECLARE @FloatVal AS Float
  3. --Assign a float value to variable
  4. SET @FloatVal = 1132.12345
  5. --Convert float value to int
  6. SELECT CONVERT(int, @FloatVal) AS ConvertedValue

SQL Convert 函数 - 图1

将Float转换为Varchar

在此示例中，我们将浮点值转换为varchar值。这个示例与上一个示例非常相似，但是唯一的区别是我们将float值转换为varchar。

  1. --Declaring a float variable
  2. DECLARE @FloatVal AS Float
  3. --Assign a float value to variable
  4. SET @FloatVal = 1132.12345
  5. --Convert float value to varchar
  6. SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue

SQL Convert 函数 - 图2
在以下示例中，我们将不向 SQL CONVERT函数的length 参数发送任何值，因此必须将length参数设置为默认值。正如我们在SQL CONVERT函数的语法说明中已经指出的那样，该默认值为30，现在我们将证明这一点。在以下查询中，我们将创建一个临时表，并将转换后的值插入该表，然后分析该表的数据结构。

  1. DROP TABLE IF EXISTS TestConvertedTable
  2. --Declaring a float variable
  3. DECLARE @FloatVal AS Float
  4. --Assign a float value to variable
  5. SET @FloatVal = 1132.12345
  6. --Convert float value to varchar
  7. SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue INTO TestConvertedTable
  8. --- Analyze the temporary table
  9. SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
  10. FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable'

SQL Convert 函数 - 图3
如上图所示，float数据类型已转换为varchar值，并且我们未设置length 参数，因此SQL Server将length参数的默认值应用 为30。
现在，我们将设置SQL CONVERT函数的length参数，然后重新检查varchar数据类型的长度。

  1. DROP TABLE IF EXISTS TestConvertedTable
  2. --Declaring a float variable
  3. DECLARE @FloatVal AS Float
  4. --Assign a float value to variable
  5. SET @FloatVal = 1132.12345
  6. --Convert float value to varchar
  7. SELECT CONVERT(varchar(20), @FloatVal) AS ConvertedValue INTO TestConvertedTable
  8. --- Analyze the temporary table
  9. SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
  10. FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable'

SQL Convert 函数 - 图4

将钱转换成Varchar

SQL Server货币数据类型有助于存储货币值。我们可以在货币值之前定义货币符号，但是SQL Server不会存储货币符号或与货币符号相关的任何数据。在以下查询中，我们将把money数据类型转换为varchar，还将使用SQL convert函数的style参数。借助style参数，我们可以确定逗号分隔符和右十进制数字的长度。下表显示了样式参数值和SQL CONVERT函数的输出。

值 Style参数 逗号分隔符 右十进制数字 输出
$4936.56 0 没有 2 4936.56
$4936.56 1 是 2 4,936.56
$4936.56 2 没有 4 4936.5600 
  1. DECLARE @Money AS MONEY=$4936.56
  3. SELECT CONVERT(Varchar,@money,0) AS [Style_0] ,
  4. CONVERT(Varchar,@money,1) AS [Style_1] ,
  5. CONVERT(Varchar,@money,2) AS [Style_2]

SQL Convert 函数 - 图5

将浮点和实数转换为Varchar

浮点和实数数据类型是SQL Server中的近似数字数据类型，这意味着这些数据类型不存储确切的值。它们存储的值非常接近。现在，我们将解释近似数值数据类型的概念。在下面的查询中，我们将创建一个包含两列的表，这些列的数据类型为float和real，然后插入一些数字值。实际上，我们期望表中有两位数字，但是实数和浮点数据类型存储的值与期望值最接近。

  1. DROP TABLE IF EXISTS TestNumericType
  2. CREATE TABLE TestNumericType (TestValFloat FLOAT,TestValReal REAL)
  5. DECLARE @ValueFloat FLOAT =0.1
  6. DECLARE @ValueReal REAL =0.1
  7. DECLARE @i INT=0
  8. WHILE @i <=1000
  9. BEGIN
  10. SET @ValueFloat=@ValueFloat + 0.1
  11. SET @ValueReal = @ValueReal + 0.1
  12. INSERT INTO TestNumericType (TestValFloat,TestValReal) VALUES(@ValueFloat,@ValueReal)
  13. SET @i = @İ+1
  14. END
  16. SELECT * FROM TestNumericType

SQL Convert 函数 - 图6
下表引用了Microsoft Docs，该表说明了float和real数据类型到文本数据的转换样式。
提示：科学记数法是一种表示非常多或很少用在科学中的数字的特殊方法。

值 输出
0（默认） 最多6位数字。适当时以科学计数法使用。
1 始终为8位数字。始终以科学计数法使用。
2 始终为16位数字。始终以科学计数法使用。
3 始终为17位数字。用于无损转换。使用这种样式，可以保证每个不同的浮点数或实数值都可以转换为不同的字符串。

现在，让我们演示一个示例，其中包含根据此表的所有数据转换样式。

  1. DECLARE @VarFl float=11234561231231.234
  3.   SELECT
  4.   CONVERT(varchar(100),@VarFl,0) AS [Style_0],
  5.   CONVERT(varchar(100),@VarFl,1) AS [Style_1],
  6.   CONVERT(varchar(100),@VarFl,2) AS [Style_2],
  7.   CONVERT(varchar(100),@VarFl,3) AS  [Style_3],
  8.   STR(CONVERT(varchar(100),@VarFl,0),20,16) as [6_Digit],
  9.   STR(CONVERT(varchar(100),@VarFl,1),20,16) as [8_Digit],
  10.   STR(CONVERT(varchar(100),@VarFl,2),20,16) as [16_Digit],
  11.   STR(CONVERT(varchar(100),@VarFl,3),20,16) as [17_Digit]

SQL Convert 函数 - 图7
如上所示，结果集显示style参数更改了SQL CONVERT函数的输出。

将日期/时间转换为Varchar

日期/时间格式可能会根据区域设置而有所不同，因此，我们需要各种格式来表示日期/时间。假设您有一个应用程序，并且有不同国家/地区的用户使用此应用程序，因此，如果有人在美国连接此应用程序，则必须将日期表示为月/日/年格式。另一方面，另一位用户在英国连接了此应用程序，您必须将日期格式表示为日/月/年格式。在这种情况下，我们必须根据区域设置日期/时间设置来确定日期/时间表示样式。sp_helplanguage存储过程在SQL Server中返回支持的语言以及详细信息。
SQL Convert 函数 - 图8
此外，我们可以确定指定会话的语言，这样我们可以将日期/时间功能调整为特定的语言。在以下查询中，我们将语言设置为波兰语，然后根据波兰语区域设置可以看到月份和日期名称的变化。

  1. SET LANGUAGE 'Polish'
  2.    SELECT DATENAME(month, DATEPART(MONTH,GETDATE())) AS [MonthName_Polish]
  3.    ,      DATENAME(WEEKDAY, DATEPART(WEEKDAY,GETDATE())) AS [DayName_Polish]

SQL Convert 函数 - 图9
提示：通常，如果要设计跨国应用程序数据库，请始终将UTC（世界标准时间）时间存储在一栏中，这样可以避免由于本地设置而引起的日期/时间问题。
SQL Server CONVERT函数提供了多个选项，可将日期/时间数据类型转换为字符数据，并且也可以通过style参数以不同的标准对字符数据输出进行样式设置，例如是否要将GETDATE内置函数结果转换为德国标准，我们可以使用以下查询：

  1. SELECT  GETDATE() AS [NotFormatedDate],CONVERT(Varchar(50),GETDATE(),104) AS [GermanStyleDate]

SQL Convert 函数 - 图10
在下面的备忘表中，您可以找到样式参数和SQL CONVERT（）函数的整体输出格式的每种用法组合，用于日期/时间到字符数据的转换。

标准 风格 输出 世纪
默认 0 mon dd yyyy hh:miAM/PM √
美国 1 mm/dd/yy
美标 2 yy.mm.dd
英国/法国 3 dd/mm/yy
德语 4 dd.mm.yy
义大利文 5 dd-mm-yy
缩短的月份名称 6 dd mon yy
缩短的月份名称 7 mon dd,yy
24小时 8 hh:mm:ss
默认值+毫秒 9 mon dd yyyy hh:mi:ss:mmmAM/PM √
美国 10 mm-dd-yy
日本 11 yy/mm/dd
ISO标准 12 yymmdd
欧洲默认值+毫秒 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 mon dd yyyy hh:miAM/PM √
我们 101 mm/dd/yyyy √
美标 102 yyyy.mm.dd √
英国/法国 103 dd/mm/yyyy √
德语 104 dd.mm.yyyy √
义大利文 105 dd-mm-yyyy √
缩短的月份名称 106 dd mon yyyy √
缩短的月份名称 107 mon dd, yyyy √
24小时 108 hh:mm:ss
默认值+毫秒 109 mon 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 √
希里 130 dd mon yyyy hh:mi:ss:mmmAM/PM √（回历）
希里 131 dd/mm/yy hh:mi:ss:mmmAM/PM √（回历）

现在，我们将使用SQL GETDATE函数的所有组合来演示该备忘表，以便我们可以确定样式参数对SQL CONVERT函数输出的影响。

  1. SELECT 'Default ' AS [Standart] ,'0' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),0)  AS [ConvertedFormat]
  2. UNION ALL
  3. SELECT 'U.S.A.' AS [Standart] ,'1' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),1)  AS [ConvertedFormat]
  4. UNION ALL
  5. SELECT 'ANSI' AS [Standart] ,'2' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),2)  AS [ConvertedFormat]
  6. UNION ALL
  7. SELECT 'British/French' AS [Standart] ,'3' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),3)  AS [ConvertedFormat]
  8. UNION ALL
  9. SELECT 'German' AS [Standart] ,'4' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),4)  AS [ConvertedFormat]
  10. UNION ALL
  11. SELECT 'Italian' AS [Standart] ,'5' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),5)  AS [ConvertedFormat]
  12. UNION ALL
  13. SELECT 'Shortened month name' AS [Standart] ,'6' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),6)  AS [ConvertedFormat]
  14. UNION ALL
  15. SELECT 'Shortened month name' AS [Standart] ,'7' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),7)  AS [ConvertedFormat]
  16. UNION ALL
  17. SELECT '24 hour time' AS [Standart] ,'8' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),8)  AS [ConvertedFormat]
  18. UNION ALL
  19. SELECT 'Default + milliseconds' AS [Standart] ,'9' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),9)  AS [ConvertedFormat]
  20. UNION ALL
  21. SELECT 'USA' AS [Standart] ,'10' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),10)  AS [ConvertedFormat]
  22. UNION ALL
  23. SELECT 'JAPAN' AS [Standart] ,'11' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),11)  AS [ConvertedFormat]
  24. UNION ALL
  25. SELECT 'ISO' AS [Standart] ,'12' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),12)  AS [ConvertedFormat]
  26. UNION ALL
  27. SELECT 'Europe default + milliseconds' AS [Standart] ,'13' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),13)  AS [ConvertedFormat]
  28. UNION ALL
  29. SELECT ' 24 hour time with milliseconds' AS [Standart] ,'14' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),14)  AS [ConvertedFormat]
  30. UNION ALL
  31. SELECT 'ODBC canonical' AS [Standart] ,'20' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),20)  AS [ConvertedFormat]
  32. UNION ALL
  33. SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'21' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),21)  AS [ConvertedFormat]
  34. UNION ALL
  35. SELECT 'Default ' AS [Standart] ,'100' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),100)  AS [ConvertedFormat]
  36. UNION ALL
  37. SELECT 'U.S.' AS [Standart] ,'101' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),101)  AS [ConvertedFormat]
  38. UNION ALL
  39. SELECT 'ANSI' AS [Standart] ,'102' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),102)  AS [ConvertedFormat]
  40. UNION ALL
  41. SELECT 'British/French' AS [Standart] ,'103' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),103)  AS [ConvertedFormat]
  42. UNION ALL
  43. SELECT 'German' AS [Standart] ,'104' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),104)  AS [ConvertedFormat]
  44. UNION ALL
  45. SELECT 'Italian' AS [Standart] ,'105' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),105)  AS [ConvertedFormat]
  46. UNION ALL
  47. SELECT 'Shortened month name' AS [Standart] ,'106' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),106)  AS [ConvertedFormat]
  48. UNION ALL
  49. SELECT 'Shortened month name' AS [Standart] ,'107' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),107)  AS [ConvertedFormat]
  50. UNION ALL
  51. SELECT ' 24 hour time' AS [Standart] ,'108' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),108)  AS [ConvertedFormat]
  52. UNION ALL
  53. SELECT 'Default + milliseconds' AS [Standart] ,'109' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),109)  AS [ConvertedFormat]
  54. UNION ALL
  55. SELECT 'USA' AS [Standart] ,'110' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),110)  AS [ConvertedFormat]
  56. UNION ALL
  57. SELECT 'JAPAN' AS [Standart] ,'111' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),111)  AS [ConvertedFormat]
  58. UNION ALL
  59. SELECT 'ISO' AS [Standart] ,'112' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),112)  AS [ConvertedFormat]
  60. UNION ALL
  61. SELECT 'Europe default + milliseconds' AS [Standart] ,'113' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),113)  AS [ConvertedFormat]
  62. UNION ALL
  63. SELECT ' 24 hour time with milliseconds' AS [Standart] ,'114' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),114)  AS [ConvertedFormat]
  64. UNION ALL
  65. SELECT 'ODBC canonical' AS [Standart] ,'120' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),120)  AS [ConvertedFormat]
  66. UNION ALL
  67. SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'121' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),121)  AS [ConvertedFormat]
  68. UNION ALL
  69. SELECT 'ISO8601' AS [Standart] ,'126' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),126)  AS [ConvertedFormat]
  70. UNION ALL
  71. SELECT 'ISO8601 with time zone Z.' AS [Standart] ,'127' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),127)  AS [ConvertedFormat]
  72. UNION ALL
  73. SELECT 'Hijri' AS [Standart] ,'130' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),130)  AS [ConvertedFormat]
  74. UNION ALL
  75. SELECT 'Hijri' AS [Standart] ,'131' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),131)  AS [ConvertedFormat]

SQL Convert 函数 - 图11
现在，我们将提及一些有关日期/时间转换操作的要点。在本节的开头，我们讨论了SQL Server语言选项和设置，因此，如果更改语言设置，它将影响某些日期/时间转换样式的输出。在下面的示例中，我们要转换GETDATE函数的结果，因此此设置直接影响SQL CONVERT函数的缩写月份名称。

  1. SET LANGUAGE lietuvių;
  3. SELECT DATEADD(YEAR,-100,GETDATE()) AS [Year_1919]
  4. ,CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate]
  6. SET LANGUAGE Croatian;
  8.   SELECT
  9. DATEADD(YEAR,100,GETDATE()) AS [Year_2119],
  10. CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate]

SQL Convert 函数 - 图12
另一个考虑因素是年份格式。在某些情况下，我们可以使用不返回世纪格式的样式参数，但是这种情况可能会引起一些混乱的情况。例如在以下示例中，我们有两个不同的日期，这些日期也存储了不同的年份，但是SQL CONVERT函数的输出是相似的。

  1. SELECT
  2. DATEADD(YEAR,-100,GETDATE()) AS [Year_1919]
  3. ,CONVERT(varchar(100),DATEADD(YEAR,-100,GETDATE()),1) AS [ConvertedDate]
  6. SELECT
  7. DATEADD(YEAR,100,GETDATE()) AS [Year_2119],
  8. CONVERT(varchar(100),DATEADD(YEAR,100,GETDATE()),1) AS [ConvertedDate]

SQL Convert 函数 - 图13

TRY_CONVERT（）

SQL TRY_CONVERT函数是SQL CONVERT函数的高级形式。SQL TRY_CONVERT函数的主要优点是可以防止查询执行期间发生数据转换错误。由于数据不合适或不干净，我们可能会在使用SQL COVERT操作的数据转换过程中遇到错误。但是，SQL TRY_CONVERT函数使我们能够避免这些类型的错误。同时，SQL CONVERT和TRY_CONVERT函数之间没有语法差异。如果数据转换生成错误，SQL TRY_CONVERT函数将返回NULL值。在下面的示例中，首先，我们将尝试通过SQL CONVERT函数将字符值转换为整数，并且该操作将返回错误。

  1. SELECT CONVERT(INT,'AnyString') AS ConvertFunc

SQL Convert 函数 - 图14
另一方面，如果我们对同一查询使用SQL TRY_CONVERT函数而不是SQL CONVERT函数，它将返回NULL值。

  1. SELECT TRY_CONVERT(INT,'AnyString') AS ConvertFunc

SQL Convert 函数 - 图15

结论

在本文中，我们彻底回顾了SQL CONVERT函数的语法，详细信息和重要注意事项。数据转换过程在实践中用途广泛，因此在本文中，我们演示了各种示例。如果在不同的国家/地区使用数据库，建议您对日期/时间数据类型转换小心。同样，格式化为日期/时间数据类型是另一个要点。

查看更多

为了提高SQL编码的生产率，请查看这些 用于SSMS和Visual Studio的SQL工具，包括T-SQL格式，重构，自动完成，文本和数据搜索，摘要和自动替换，SQL代码和对象比较，多数据库脚本比较，对象解密等。