SQL_SERVER CAST 和 CONVERT

语法

CAST ( expression AS data_type [ ( length ) ] )

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

 日期和时间样式

转换日期和时间数据类型

-- 转换日期和时间数据类型
DECLARE @d1 DATE, @t1 TIME, @dt1 DATETIME;

SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();

SELECT @d1                   AS [DATE],             -- 2023-11-16
       CAST(@d1 AS DATETIME) AS [date as datetime], -- 2023-11-16 00:00:00.000
       @t1                   AS [TIME],             -- 16:49:26.1900000
       CAST(@t1 AS DATETIME) AS [time as datetime], -- 1900-01-01 16:49:26.190
       @dt1                  AS [DATETIME],         -- 2023-11-16 16:49:26.190
       CAST(@dt1 AS DATE)    AS [datetime as date], -- 2023-11-16
       CAST(@dt1 AS TIME)    AS [datetime as time]; -- 16:49:26.1900000

使用 CONVERT 处理不同格式的 datetime 数据

-- 使用 CONVERT 处理不同格式的 datetime 数据
SELECT CONVERT(NVARCHAR, GETDATE(), 20), -- 2023-11-16 16:53:11
       CONVERT(NVARCHAR, GETDATE(), 23) -- 2023-11-16

 

 附:

CONVERT style 日期和时间样式

https://learn.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2016#date-and-time-styles