SQL Server CONVERT()

SQL Server 的 CONVERT() 函数用于在数据类型之间进行转换，是数据类型转换的重要函数。

一、基本语法

CONVERT(data_type(length), expression, style)

-- data_type: 目标数据类型
-- expression: 要转换的值或表达式
-- style: 日期时间转换的格式样式（可选）

二、常用数据类型转换

转换为字符串

-- 整数转字符串
SELECT CONVERT(VARCHAR(10), 12345) AS Result
-- 结果: '12345'

-- 小数转字符串
SELECT CONVERT(VARCHAR(20), 123.456) AS Result
-- 结果: '123.456'

-- 日期转字符串（指定样式）
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS Result
-- 结果: '2024-01-15'

-- 货币转字符串
SELECT CONVERT(VARCHAR(20), 1234.56) AS Result

转换为数字

-- 字符串转整数
SELECT CONVERT(INT, '12345') AS Result
-- 结果: 12345

-- 字符串转小数
SELECT CONVERT(DECIMAL(10,2), '123.45') AS Result
-- 结果: 123.45

-- 字符串转浮点数
SELECT CONVERT(FLOAT, '123.456') AS Result

-- 货币字符串转数字
SELECT CONVERT(MONEY, '$1,234.56', 1) AS Result

转换为日期时间

-- 字符串转日期
SELECT CONVERT(DATE, '2024-01-15') AS Result
SELECT CONVERT(DATETIME, '2024-01-15 14:30:00') AS Result

-- 数字转日期
SELECT CONVERT(DATETIME, 44562) AS Result
-- 结果: 2022-01-01 00:00:00.000

三、日期时间格式样式

常用样式代码

样式	格式	示例
1	mm/dd/yy	01/15/24
101	mm/dd/yyyy	01/15/2024
2	yy.mm.dd	24.01.15
102	yyyy.mm.dd	2024.01.15
3	dd/mm/yy	15/01/24
103	dd/mm/yyyy	15/01/2024
4	dd.mm.yy	15.01.24
104	dd.mm.yyyy	15.01.2024
5	dd-mm-yy	15-01-24
105	dd-mm-yyyy	15-01-2024
6	dd mon yy	15 Jan 24
106	dd mon yyyy	15 Jan 2024
7	mon dd, yy	Jan 15, 24
107	mon dd, yyyy	Jan 15, 2024
8	hh:mm:ss	14:30:00
108	hh:mm:ss	14:30:00
9	mon dd yyyy hh:mm:ss:mmmAM	Jan 15 2024 2:30:00:123PM
109	mon dd yyyy hh:mm:ss:mmmAM	Jan 15 2024 2:30:00:123PM
10	mm-dd-yy	01-15-24
110	mm-dd-yyyy	01-15-2024
11	yy/mm/dd	24/01/15
111	yyyy/mm/dd	2024/01/15
12	yymmdd	240115
112	yyyymmdd	20240115
20	yyyy-mm-dd hh:mm:ss	2024-01-15 14:30:00
21	yyyy-mm-dd hh:mm:ss.mmm	2024-01-15 14:30:00.123
120	yyyy-mm-dd hh:mm:ss	2024-01-15 14:30:00
121	yyyy-mm-dd hh:mm:ss.mmm	2024-01-15 14:30:00.123
126	yyyy-mm-ddThh:mm:ss.mmm	2024-01-15T14:30:00.123
127	yyyy-mm-ddThh:mm:ss.mmmZ	2024-01-15T14:30:00.123Z

四、日期转换示例

不同格式的日期转换

DECLARE @Now DATETIME = GETDATE()

-- 美国格式
SELECT CONVERT(VARCHAR(20), @Now, 101) AS US_Format      -- mm/dd/yyyy
SELECT CONVERT(VARCHAR(20), @Now, 101) AS Result
-- 结果: 01/15/2024

-- 英国/法国格式
SELECT CONVERT(VARCHAR(20), @Now, 103) AS UK_Format      -- dd/mm/yyyy
-- 结果: 15/01/2024

-- 日本/ISO格式
SELECT CONVERT(VARCHAR(20), @Now, 111) AS Japan_Format    -- yyyy/mm/dd
-- 结果: 2024/01/15

-- ISO 8601 格式
SELECT CONVERT(VARCHAR(20), @Now, 120) AS ISO_Format      -- yyyy-mm-dd hh:mm:ss
-- 结果: 2024-01-15 14:30:00

-- 紧凑格式（无分隔符）
SELECT CONVERT(VARCHAR(20), @Now, 112) AS Compact_Format  -- yyyymmdd
-- 结果: 20240115

-- 带时间的完整格式
SELECT CONVERT(VARCHAR(30), @Now, 121) AS Full_Format     -- yyyy-mm-dd hh:mm:ss.mmm
-- 结果: 2024-01-15 14:30:00.123

字符串转日期

-- 不同格式的字符串转日期
SELECT CONVERT(DATE, '01/15/2024', 101) AS US_To_Date
SELECT CONVERT(DATE, '15/01/2024', 103) AS UK_To_Date
SELECT CONVERT(DATE, '2024-01-15', 120) AS ISO_To_Date
SELECT CONVERT(DATE, '20240115', 112) AS Compact_To_Date

五、CAST 与 CONVERT 对比

-- CAST（ANSI标准）
SELECT CAST('123' AS INT) AS CastResult
SELECT CAST(GETDATE() AS VARCHAR(20)) AS CastDate

-- CONVERT（SQL Server特定，支持样式）
SELECT CONVERT(VARCHAR(20), GETDATE(), 120) AS ConvertDate

-- 性能比较（CONVERT 略快）
SET STATISTICS TIME ON
SELECT CAST('123' AS INT) FROM sys.objects
SELECT CONVERT(INT, '123') FROM sys.objects
SET STATISTICS TIME OFF

六、实际应用示例

1. 格式化报表日期

-- 生成报表标题格式
SELECT 
    CONVERT(VARCHAR(10), OrderDate, 103) AS OrderDate_UK,
    CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDate_US,
    CONVERT(VARCHAR(8), OrderDate, 112) AS OrderDate_YYYYMMDD,
    'Report for ' + CONVERT(VARCHAR(10), GETDATE(), 106) AS ReportTitle
FROM Orders

2. 数字格式化

-- 带千位分隔符
SELECT CONVERT(VARCHAR(20), CONVERT(MONEY, 1234567.89), 1) AS FormattedNumber
-- 结果: 1,234,567.89

-- 百分比格式
SELECT 
    ProductName,
    CAST(CAST(SalesAmount * 100.0 / TotalSales AS DECIMAL(5,2)) AS VARCHAR) + '%' AS Percentage
FROM SalesData

3. 数据类型转换处理

-- 安全转换（处理可能的错误）
DECLARE @Value VARCHAR(50) = '123.45'

-- 使用 TRY_CONVERT (SQL Server 2012+)
SELECT TRY_CONVERT(INT, @Value) AS IntResult      -- NULL (转换失败)
SELECT TRY_CONVERT(DECIMAL(10,2), @Value) AS DecimalResult  -- 123.45

-- 使用 CASE 处理
SELECT 
    CASE 
        WHEN ISNUMERIC(@Value) = 1 THEN CONVERT(DECIMAL(10,2), @Value)
        ELSE 0
    END AS SafeConvert

4. 动态列名或表名

-- 创建动态表名（按年月）
DECLARE @YearMonth VARCHAR(6) = CONVERT(VARCHAR(6), GETDATE(), 112)
DECLARE @TableName NVARCHAR(100) = 'Sales_' + @YearMonth

-- 生成日志文件名
DECLARE @LogDate VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112)
DECLARE @LogFile VARCHAR(100) = 'C:\Logs\Application_Log_' + @LogDate + '.txt'

5. 数据清洗示例

-- 清洗导入数据
CREATE PROCEDURE sp_CleanImportData
AS
BEGIN
    -- 去除空格并转换类型
    UPDATE ImportTable
    SET 
        CleanedInt = TRY_CONVERT(INT, LTRIM(RTRIM(RawIntData))),
        CleanedDate = TRY_CONVERT(DATE, RawDateData, 103),  -- dd/mm/yyyy格式
        CleanedDecimal = TRY_CONVERT(DECIMAL(18,2), REPLACE(RawDecimalData, ',', '')),
        CleanedPhone = CONVERT(VARCHAR(15), TRY_CONVERT(BIGINT, RawPhoneData))
    WHERE IsProcessed = 0
END

6. 报告生成示例

-- 生成客户报表
SELECT 
    CustomerID,
    CustomerName,
    CONVERT(VARCHAR(10), RegistrationDate, 103) AS RegistrationDate,
    CONVERT(VARCHAR(20), CONVERT(MONEY, TotalPurchases), 1) AS TotalPurchases,
    CONVERT(VARCHAR(10), LastOrderDate, 103) AS LastOrderDate,
    CONVERT(VARCHAR(10), DATEDIFF(DAY, LastOrderDate, GETDATE())) + ' days' AS DaysSinceLastOrder,
    CONVERT(VARCHAR(20), CONVERT(MONEY, AverageOrderValue), 1) AS AverageOrderValue
FROM Customers
WHERE LastOrderDate >= DATEADD(MONTH, -6, GETDATE())
ORDER BY TotalPurchases DESC

七、常见错误处理

转换错误示例

-- 可能导致错误的转换
-- SELECT CONVERT(INT, 'ABC')  -- 错误：转换失败

-- 安全的转换方法
SELECT 
    TRY_CONVERT(INT, 'ABC') AS SafeResult,           -- NULL
    ISNUMERIC('ABC') AS IsNumeric,                   -- 0
    TRY_CAST('ABC' AS INT) AS TryCastResult          -- NULL (SQL Server 2012+)

处理 NULL 值

-- 处理 NULL 转换
SELECT 
    ISNULL(CONVERT(VARCHAR(20), NULL), 'Unknown') AS ConvertedValue,
    COALESCE(CONVERT(VARCHAR(20), NULL), 'Not Available') AS AlternativeValue

八、性能优化建议

1. 避免在 WHERE 子句中使用转换

-- 不好的做法（无法使用索引）
SELECT * FROM Orders 
WHERE CONVERT(VARCHAR(10), OrderDate, 112) = '20240115'

-- 好的做法（可以使用索引）
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-15' AND OrderDate < '2024-01-16'

2. 数据类型优先级

-- SQL Server 自动转换（可能影响性能）
SELECT * FROM Orders 
WHERE OrderID = '12345'  -- 隐式转换

-- 明确转换（更好）
SELECT * FROM Orders 
WHERE OrderID = CONVERT(INT, '12345')

九、特殊用途

二进制转换

-- 转换为十六进制
SELECT CONVERT(VARBINARY(20), 'Hello World') AS BinaryData
SELECT CONVERT(VARCHAR(100), CONVERT(VARBINARY(20), 'Hello World'), 1) AS HexString

-- 查看数据类型
SELECT 
    SQL_VARIANT_PROPERTY(123, 'BaseType') AS DataType,
    SQL_VARIANT_PROPERTY(CONVERT(DECIMAL(10,2), 123), 'Precision') AS Precision

CONVERT() 在数据仓库、报表系统、数据迁移等场景中非常重要，合理使用可以大大简化数据处理逻辑。