T-SQL——函数——时间操作函数
目录
志铭-2021年10月31日 22:57:15
0. 日期和时间类型
0.0 时间类型
| 数据类型 | 时间范围 | 准确度 | 推荐格式 | 示例 |
|---|---|---|---|---|
| DATETIME | 1753-01-01到9999-12-31 | 3.33毫秒 | 'YYYYMMDD hh:mm:ss.nnn' | 2021-10-23 14:35:20.727 |
| SMALLDATETIME | 1900-01-01到2079-06-06 | 1分钟 | 'YYYYMMDD hh:mm' | 2021-10-23 14:41:00 |
| DATE | 0001-01-01到9999-12-31 | 1天 | 'YYYY-MM-DD' | 2021-10-23 |
| TIME | 00:00:00:0000000到23:59:59.9999999 | 100纳秒 | 'hh:mm:ss:nnnnnnn' | 14:44:35.4170000 |
| DATETIME2 | 0001-01-01 00:00:00:0000000到9999-12-31 23:59:59.9999999 | 100纳秒 | 'YYYY-MM-DD hh:mm:ss.nnnnnnn' | 2021-10-23 14:46:10.9600000 |
| DATETIMEOFFSET | 0001-01-01 00:00:00:0000000到9999-12-31 23:59:59.9999999 | 100纳秒 | 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-] hh:mm' | 2021-10-23 14:48:30.8730000 +00:00 |
- [注1] SQL Server2008之前的时间类型只有 DATETIME 和 SMALLDATETIME ，2008之后才出现日期和时间的单独类型
- [注2] DATETIME2比DATETIME的范围变大了，精确杜也提高了
- [注3] 推荐格式只是推荐格式，其上任何日期类型，都是即支持“YYYY-MM-DD”也是支持“YYYYMMDD”
- [注4] DATETIMEOFFSET中的 +/- hh:mm表示的时区差
- UTC时间：世界协调时间，当今世界的标准时间
- UTC+时区差=本地时间，时区：东为正，西为负
- 北京时区是东八区，领先UTC时间8个小时
简单测试，有一个直观的理解：
DECLARE @dateTime DATETIME = GETDATE();
SELECT @dateTime;
--结果：2021-10-23 11:53:56.420
DECLARE @smallDateTime SMALLDATETIME =GETDATE();
SELECT @smallDateTime
--结果：2021-10-23 11:54:00
DECLARE @date DATE=GETDATE();
SELECT @date
--结果：2021-10-23
DECLARE @time TIME =GETDATE();
SELECT @time
--结果：11:53:56.4200000
DECLARE @dateTime2 DATETIME2 =GETDATE();
SELECT @dateTime2
--结果：2021-10-23 11:53:56.4200000
DECLARE @dateTimeOffset DATETIMEOFFSET =GETDATE();
SELECT @dateTimeOffset
--结果：2021-10-23 11:53:56.4200000 +00:00
1. 转换函数
1.1 CAST
-
【说明】 将指定的表达式转换为目标数据类型。这里有一个前提就是指定表达式理论上可以转换为你指定的目标类型，负责报错
-
【语法】
CAST( expressionString AS dataType) -
【示例】
SELECT CAST('012' AS INT)返回：12SELECT CAST('1.23' AS INT)返回：在将 varchar 值 '1.23' 转换成数据类型 int 时失败。
1.2 CONVERT
-
【说明】 其作用和CAST相同
- 但是CONVERT可以设置第三个参数来指定转换的样式，所以可以通过该参数将特定的时间字符串转为特定的时间格式
- CAST是标准SQL，而CONVERT不是标准SQL
-
【语法】
COVNERT(dataType,expressionString,[style_number]) -
【示例】
SELECT CONVERT(INT, '012')返回：12- TODO……
2. 日期操作函数
2.0 GETDATE和GETUTCDATE
-
【说明】 GETDATE和GETUTDATE两个函数都是用于返回datetime类型的当前日期和时间
- GETUTDATE()是使用数据库服务器上的时区设置来求UTC时间
-
【示例】
SELECT GETDATE() --结果：2021-10-23 16:59:59.917 SELECT GETUTCDATE() --结果：2021-10-23 08:59:59.917 --可以直白的看到当前的北京时间（东八区）比世界协调时间快8个小时
2.1 SYSDATETIME和SYSUTCDATETIME
-
【说明】返回当前时间。等价于GERDATE和GETUTCDATE,这是在SQL Server2008中新增的，返回的2008中增加的DATETIME2类型的结果
-
【示例】
SELECT SYSDATETIME() --结果：2021-10-23 17:01:54.9879870 SELECT SYSUTCDATETIME() --结果：2021-10-23 09:01:54.9879870
2.2 DATEADD
-
【说明】 用于在日期值上加上指定单位指定的间隔
- 注意间隔值可以是负数，从而实现减去指定单位指定的间隔
-
【语法】
DATEADD(datePart,number,date)时间间隔 参数 年 Year，yyyy,yy 季度 Quarter,qq,q 月 Month,mm,m 一年内的天 DayOfYear,dy,y 天 Day,dd,d 星期 Week,wk,www -
【示例】
--当前日期加1天 SELECT DATEADD(Day,1,GETDATE()) --当前日期减1天 SELECT DATEADD(Day,-1,GETDATE())
2.3 DATEDIFF
-
【说明】 按照指定的间隔单位，计算两个日期之间的间隔
-
【语法】
DATEDIFF(datePart,startDate,endDate)
注意：其中的datePart和DATEADD中的间隔单位是一样的 -
【示例｝
--计算两个日期之间间隔几天 SELECT DATEDIFF(DAY,'2021-01-01','2021-01-02')--结果：1 --计算两个日期之间间隔几个月 SELECT DATEDIFF(MONTH,'2020-01-01','2021-01-02')--结果：12
2.4 DATEPART和DATENAME
-
【说明】获取日期中的指定部分，如年，月，日，星期
中文环境中二者不同之处:
DATEPART返回的是一个整形，
DATENAME返回的是一个字符串类型（若是英文环境下，比如返回月份，则直接返回月份的名称） -
【语法】
DATEPART(datePart,dateString)
DATENAME(datePart,dateString) -
【示例】
SELECT DATENAME(MONTH,'2021-01-01')--结果：1（字符串类型） SELECT DATEPART(MONTH,'2021-01-01')--结果：1（整型） SELECT DATEPART(WEEKDAY,'2023-02-18')--结果：7 改日为周六，注：该函数的一周的第一天是周日
2.5 YEAR、MONTH和DAY
-
【说明】 获取指定日期中的中指定的部分，如 年，月，日
可以认为是DATEPART的简化函数 -
【语法】
YEAR(dateString)
MONTH(dateString)
DAY(dateString) -
【示例】
SELECT YEAR('20211031')--结果：2021 SELECT MONTH('20211031')--结果：10 SELECT DAY('20211031')--结果：31
2.6 ISDATE
-
【说明】判断指定的字符串是否可以转换为时间格式
-
【语法】
ISDATE(dateString) -
【示例】
SELECT ISDATE('20211031')--结果：1 SELECT ISDATE('20211031 22:46:01:01')--结果：1 SELECT ISDATE('2021-10-31')--结果：1 SELECT ISDATE('2021/10/31')--结果：1 SELECT ISDATE('2021-10-32')--结果：0
3. 综合使用示例
3.1 根据出生日期计算年龄
CREATE TABLE #temptable
(
[Name] VARCHAR(4),
[Brithday] VARCHAR(10)
);
INSERT INTO #temptable
VALUES
('张三', '1994-01-01'),
('李四','1994-12-01');--注：测试日期是2021年10月31日
SELECT Name,
DATEDIFF(YEAR, Brithday, GETDATE()) AS Age,--精确到年
CONVERT(INT,DATEDIFF(DAY,Brithday,GETDATE())/365.25) AS Age--精确到日
FROM #temptable;
--结果：执行这段语句的日期是2021年10月31日，故李四还没有到今年的生日，所以是26
--Name Age Age
------ -------- ---------
--张三 27 27
--李四 27 26
