SQL Server日期函数总结

ReactSpring
529 阅读7分钟
获得一个月的天数

思路：首先到得一个月最后一天的日期，通过 SQL Server 日期函数 day() 取得日期中的“天 ”部分

 -- 获得 2008 年 2 月份的天数：
 select day(cast('2008-03-01' as datetime) - 1)
获得本月天数：

返回一个整数，该整数表示指定的 date 是该月份的哪一天。

 select day(dateadd(month,1,getdate()) - day(getdate()))
获得上个月天数：
 select day(getdate()-day(getdate()))
 -- 注：SQL Server 日期函数 day(@date) 等价于日期函数 datepart(day,@date)
获得当前系统日期、时间：
 select getdate()
在指定日期加上一段时间的基础上，返回新的datetime值
 -- 向日期加上2天
 select dateadd(day, 2, '2004-10-15') 
 -- 返回：2004-10-17 00:00:00.000
查询某年的数据
 select * from table where YEAR(createTime) = 2018;
查询某月的数据 将数据库中的时间字段格式改为字符串格式
 select * from combine where CONVERT(varchar(7),create_time, 120 ) = '2018-05';
 -- OR
 select * from combine where YEAR(create_time)=2018 and month(create_time)=5;
查询昨天的数据:
 select * from table where datediff(day, 时间字段,getdate()) = 1
查询今天的数据:
 SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=0;
查询昨天的数据:
 SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=1;
查询本周的数据:
 SELECT * FROM checkinfo where datediff(week,c_sample_date,getdate())=0;
查询上周的数据:
 select * from checkinfo where datediff(week, 时间字段 ,getdate()) = 1;
查询下周的数据:
 select * from checkinfo where datediff(week, 时间字段 ,getdate()) = -1;
查询上月的数据:
 select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 1;
查询本月的数据:
 select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 0;
查询下月的数据:
 Select * From checkinfo Where DateDiff(month,时间字段,GetDate()) = -1;
查询最近七天的数据:
 Select * From checkinfo Where DateDiff(dd, 时间字段, GetDate()) <= 7
查询今年的数据:
 Select * From checkinfo Where DateDiff(year, GetDate(), c_sample_date ) = 0
获取上月的第一天
 SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)
获取上月的最后一天
 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+' 23:59:59';
获取当月第一天
 select dateadd(month, datediff(month, 0, getdate()), 0)
获取当月最后一天

思路：下月的第一天减去一天

 select dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1)
获取下月的第一天
 SELECT CONVERT(CHAR(10),DATEADD(m,1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)
获取来月的最后一天
 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+' 23:59:59';
获取 当年第一天
 select dateadd(year, datediff(year, 0, getdate()), 0)
获取当年最后一天

思路：下年的第一天减去一天

 select dateadd(year, datediff(year, 0, dateadd(year, 1, getdate())), -1)
获取当天零时 2018-10-15 00:00:00.000
 select dateadd(day, datediff(day, 0, getdate()), 0)
 -- 解释：函数datediff(month, 0, getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数。记住：日期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。下一个函数是DATEADD，增加当前日期到“1900-01-01”的月数。通过增加预定义的日期“1900-01-01”和当前日期的月数，我们可以获得这个月的第一天。另外，计算出来的日期的时间部分将会是“00:00:00.000”。这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用来计算很多不同的日期。
上月最后一天 :
 select dateadd(month,datediff(month,-1,('2020-05'+'-01'))-1,-1) -- 2020-04-30
上月第一天 :
 select dateadd(month,datediff(month,0,('2020-05'+'-01'))-1,0) -- 2020-04-01
去年当月最后一天 :
 select dateadd(year,-1,(dateadd(month, datediff(month, -1, '2020-05'+'-01'), -1))) -- 2019-05-31
去年当月第一天:
 select dateadd(year,-1,('2020-05'+'-01')) -- 2019-05-01
返回跨两个指定日期的日期和时间边界数：
 select datediff(day,'2004-09-01','2004-09-18') -- 17
返回代表指定日期的指定日期部分的整数：
 SELECT DATEPART(month, '2004-10-15') -- 10
返回代表指定日期的指定日期部分的字符串
 SELECT datename(weekday, '2004-10-15') -- 星期五
 -- day(), month(), year() 可以与datepart对照一下
值 缩 写（Sql Server） (Access 和 ASP) 说明
 Year Yy yyyy 年 1753 ~ 9999
 Quarter Qq q 季 1 ~ 4
 Month Mm m 月1 ~ 12
 Day of year Dy y 一年的日数,一年中的第几日 1-366
 Day Dd d 日，1-31
 Weekday Dw w 一周的日数，一周中的第几日 1-7
 Week Wk ww 周，一年中的第几周 0 ~ 51
 Hour Hh h 时0 ~ 23
 Minute Mi n 分钟0 ~ 59
 Second Ss s 秒 0 ~ 59
 Millisecond Ms - 毫秒 0 ~ 999
 -- 举例：
 -- GetDate() 用于sql server :select GetDate()
 -- DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
 -- DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天
 -- DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
 -- DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
 -- DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
 -- DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年
善用 SQL Server 中的 CONVERT 函数处理日期字串:
 SELECT CONVERT(char(19), getdate(), 120) -- 2008-02-27 00:25:13
 SELECT CONVERT(char(10), getdate(), 12) -- 2008-02-27
 SELECT CONVERT(char(10), getdate(), 102) -- 2008.02.27
 SELECT CONVERT(char(8), getdate(), 2) -- 08.02.27
 SELECT CONVERT(char(10), getdate(), 111) -- 2008/02/27
 SELECT CONVERT(char(8), getdate(), 11) -- 08/02/27
 SELECT CONVERT(char(8), getdate(), 112) -- 20080227
 SELECT CONVERT(char(6), getdate(), 12) -- 080227
 在数据库取出来的时候就转换好
 select getdate() -- 2006-05-12 11:06:08.177
 select Convert(varchar(10),getdate(),120) -- 2006-05-12
 select CONVERT(varchar, getdate(), 120 ) -- 2006-05-12 11:06:08
 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') -- 20060512110608
 select CONVERT(varchar(12) , getdate(), 111 ) -- 2006/05/12
 select CONVERT(varchar(12) , getdate(), 112 ) -- 20060512
 select CONVERT(varchar(12) , getdate(), 102 ) -- 2006.05.12
其它几种不常用的日期格式转换方法:
 select CONVERT(varchar(12) , getdate(), 101 ) -- 0612/2005 
 select CONVERT(varchar(12) , getdate(), 103 ) -- 12/09/2004
 select CONVERT(varchar(12) , getdate(), 104 ) -- 12.05.2006
 select CONVERT(varchar(12) , getdate(), 105 ) -- 12-05-2006
 select CONVERT(varchar(12) , getdate(), 106 ) -- 12 05 2006
 select CONVERT(varchar(12) , getdate(), 107 ) -- 05 12, 2006
 select CONVERT(varchar(12) , getdate(), 108 ) -- 11:06:08
 select CONVERT(varchar(12) , getdate(), 109 ) -- 0512 2006 1
 select CONVERT(varchar(12) , getdate(), 110 ) -- 09-12-2004
 select CONVERT(varchar(12) , getdate(), 113 ) -- 12 052006
 select CONVERT(varchar(12) , getdate(), 114 )
Style IDStyle 格式
100 或者 0mon dd yyyy hh:miAM （或者 PM）
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109或者9mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110mm-dd-yy
111yy/mm/dd
112yymmdd
113或13dd mon yyyy hh:mm:ss:mmm(24h)
114hh:mi:ss:mmm(24h)
120或者20yyyy-mm-dd hh:mi:ss(24h)
121或者21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yy hh:mi:ss:mmmAM
avatar
文章
阅读
粉丝