SQLSERVER 日期和时间
日期和时间数据类型
| 数据类型 | 格式 | 范围 | 精确度 | 存储大小（字节） | 用户定义的秒的小数部分精度 | 时区偏移量 |
|---|---|---|---|---|---|---|
| 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
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
-- 转换日期和时间数据类型
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 |
官方文档:
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2016
