SQLSERVER 日期和时间-优快云博客

本文链接：https://blog.youkuaiyun.com/xc_oo0/article/details/135523533

日期和时间数据类型

数据类型	格式	范围	精确度	存储大小(字节)	用户定义的秒的小数部分精度	时区偏移量
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 到 23:59:59.9999999	100 纳秒	3 到 5	是	否
date	YYYY-MM-DD	0001-01-01 到 31.12.99	1 天	3	否	否
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 到 2079-06-06	1 分钟	4	否	否
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 到 9999-12-31	0.00333 秒	8	否	否
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999	100 纳秒	6 到 8	是	否
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+\|-]hh:mm	0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999(以 UTC 时间表示)	100 纳秒	8 到 10	是	是

日期和时间函数

系统日期和时间函数

-- 系统日期和时间函数
SELECT SYSDATETIME()                -- 2023-11-16 15:36:52.0075417
     , SYSDATETIMEOFFSET()          -- 2023-11-16 15:36:52.0075417+8
     , SYSUTCDATETIME()             -- 2023-11-16 07:36:52.0075417
     , CURRENT_TIMESTAMP            -- 2023-11-16 15:36:52.007
     , GETDATE()                    -- 2023-11-16 15:36:52.007
     , GETUTCDATE()                 -- 2023-11-16 07:36:52.007
     , CONVERT(DATE, SYSDATETIME()) -- 2023-11-16
     , CONVERT(TIME, SYSDATETIME()) -- 17:04:00.3993961

返回日期和时间部分的函数

-- 返回日期和时间部分的函数
SELECT DATENAME(year, SYSDATETIME())      -- 2023  year, yyyy, yy
     , YEAR(SYSDATETIME())                -- 2023
     , DATENAME(quarter, SYSDATETIME())   -- 4 quarter, qq, q
     , DATENAME(month, SYSDATETIME())     -- 11 month, mm, m
     , MONTH(SYSDATETIME())               -- 11
     , DATENAME(day, SYSDATETIME())       -- 16 day, dd, d
     , DAY(SYSDATETIME())                 -- 16
     , DATENAME(dayofyear, SYSDATETIME()) -- 320 dayofyear, dy, y
     , DATENAME(week, SYSDATETIME())      -- 46 week, wk, ww
     , DATENAME(weekday, SYSDATETIME())   -- 星期四 weekday, dw
     , DATEPART(weekday, SYSDATETIME())   -- 5 weekday, dw
     , DATENAME(hour, SYSDATETIME())      -- 17 hour, hh
     , DATENAME(minute, SYSDATETIME())    -- 13 minute, n
     , DATENAME(second, SYSDATETIME()) -- 48 second, ss, s

从相应部分返回日期和时间值的函数

-- 从相应部分返回日期和时间值的函数
SELECT DATEFROMPARTS(2023, 11, 16)                                    -- 2023-11-16
     , DATETIME2FROMPARTS(2023, 11, 16, 18, 08, 20, 0, 0)             -- 2023-11-16 18:08:20
     , DATETIME2FROMPARTS(2023, 11, 16, 18, 08, 20, 5, 1)             -- 2023-11-16 18:08:20.5 5/10秒
     , DATETIME2FROMPARTS(2023, 11, 16, 18, 08, 20, 50, 2)            -- 2023-11-16 18:08:20.50 50/100秒
     , DATETIME2FROMPARTS(2023, 11, 16, 18, 08, 20, 500, 3)           -- 2023-11-16 18:08:20.500 500/1000秒
     , DATETIMEFROMPARTS(2023, 11, 16, 18, 08, 20, 0)                 -- 2023-11-16 18:08:20.000
     , DATETIMEOFFSETFROMPARTS(2023, 11, 16, 18, 08, 20, 0, 12, 0, 7) -- 2023-11-16 18:08:20.0000000+12
     , SMALLDATETIMEFROMPARTS(2023, 11, 16, 18, 08)                   -- 2023-11-16 18:08:00
     , TIMEFROMPARTS(18, 08, 20, 0, 0)                                -- 18:08:20
     , TIMEFROMPARTS(18, 08, 20, 5, 1) -- 18:08:20.5

返回日期和时间差异值的函数

-- 返回日期和时间差异值的函数
select DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF_BIG(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(weekday, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
     , DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

修改日期和时间值的函数

-- 修改日期和时间值的函数
SELECT DATEADD(month, 1, '20060830')   -- 2006-09-30 00:00:00.000
     , DATEADD(month, 1, '2006-08-31') -- 2006-09-30 00:00:00.000
     , EOMONTH(SYSDATETIME())          -- 2023-11-30 月最后一天
     , EOMONTH('2023-11-17')           -- 2023-11-30
     , EOMONTH('2023-11-17', 1)        -- 2023-12-31
     , SWITCHOFFSET(CONVERT(datetimeoffset, GETDATE()), '-08:00') -- 时区偏移 2023-11-17 06:36:35.5370000+65528

设置或返回会话格式函数的函数

-- 设置或返回会话格式函数的函数
-- sp_helplanguage;
-- SET DATEFIRST 3;
-- SET LANGUAGE 简体中文;
-- SET LANGUAGE us_english;
-- SET DATEFORMAT ymd;
select @@DATEFIRST
     , @@LANGUAGE

验证日期和时间值的函数

-- 是否为有效的日期或时间值
select ISDATE('2009-05-12 10:19:41.177')

FORMAT

-- FORMAT
SELECT FORMAT(GETDATE(), 'd', 'en-US')          -- 11/17/2023
     , FORMAT(GETDATE(), 'd', 'zh-cn')          -- 2023/11/17
     , FORMAT(GETDATE(), 'D', 'en-US')          -- Friday, November 17, 2023
     , FORMAT(GETDATE(), 'D', 'zh-cn')          -- 2023年11月17日
     , FORMAT(GETDATE(), 'yyyy-MM-dd', 'zh-cn') -- 2023-11-17
     , FORMAT(cast('17:35' as time), N'hh\:mm') -- 17:35
     , FORMAT(SYSDATETIME(), N'HH\:mm tt') -- 15:12 PM

CAST 和 CONVERT (Transact-SQL)

-- 转换日期和时间数据类型
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 数据
SELECT CONVERT(NVARCHAR, GETDATE(), 20), -- 2023-11-16 16:53:11
       CONVERT(NVARCHAR, GETDATE(), 23) -- 2023-11-16

附:

datepart参数

https://learn.microsoft.com/zh-cn/sql/t-sql/functions/datename-transact-sql?view=sql-server-2016#examples

datepart	返回值
year, yyyy, yy	2007
quarter, qq, q	4
month, mm, m	10 月
dayofyear, dy, y	303
day, dd, d	30
week, wk, ww	44
weekday, dw	星期二
hour, hh	12
minute, n	15
second, ss, s	32
millisecond, ms	123
microsecond, mcs	123456
nanosecond, ns	123456700
TZoffset, tz	+05:10
ISO_WEEK, ISOWK, ISOWW	44