1. 日期比较 :DATEDIFF
-- 年: 返回 10
SELECT DATEDIFF(yyyy,'2008-08-05','2018-08-05') AS DiffDate
-- 月: 返回 7
SELECT DATEDIFF(mm,'2008-01-05','2008-08-05') AS DiffDate
-- 日: 返回 1
SELECT DATEDIFF(dd,'2008-08-05','2008-08-06') AS DiffDate
-- 小时: 返回 24
SELECT DATEDIFF(hh,'2008-08-05','2008-08-06') AS DiffDate
-- 分钟: 返回 24
SELECT DATEDIFF(mi,'2008-08-05','2008-08-06') AS DiffDate
-- 秒: 返回 86400
SELECT DATEDIFF(ss,'2008-08-05','2008-08-06') AS DiffDate
2. 日期作为条件
-- 作为Where 直接比较
select col from dataTable where currDate = CONVERT(VARCHAR(19),GETDATE(),120)
或者
select col from dataTable where currDate = '2018-09-12'
或者
select col from dataTable where currDate = '2018-09-12 10:10:10'
3. 日期加减
-- 天数相加:返回2008-08-15 00:00:00.000
select DATEADD(dd,10,'2008-08-05') as DATEADD
-- 月相加:返回2008-10-05 00:00:00.000
select DATEADD(mm,10,'2008-08-05') as DATEADD
-- 年相加:返回2010-08-05 00:00:00.000
select DATEADD(yyyy,2,'2008-08-05') as DATEADD
4.日期转换
-- 第一个参数指定转换的类型,第二个参数转换的值,第三个参数转换的格式(日期)
-- 返回值: 2019-03-22 10:04:12
select CONVERT(VARCHAR(19),GETDATE(),120) CONVERTDate
-- 返回值: 2019-03-22 10:04:12.222
select CONVERT(VARCHAR(19),GETDATE(),121) CONVERTDate
-- 返回值: 10:04:12
select CONVERT(VARCHAR(10),GETDATE(),108) CONVERTDate
--返回值: 2019/03/22
select CONVERT(VARCHAR(10),GETDATE(),111) CONVERTDate