sql server学习笔录5（T-SQL 日期处理总结）

日期的格式、推算、运算是平时常用的，简单易错。

1. T-SQL 日期格式转换

由原始的值2012-04-03 10:57:06.127转换成以下20种常用的格式

2012-04-03
20120403
2012.04.03
04/03/2012
03/04/2012
03.04.2012
03-04-2012
03 Apr 2012
Apr 03, 2012
10:57:06
Apr  3 2012
04-03-2012
2012/04/03
20120403
03 Apr 2012
10:57:06:127
2012-4-3
2012年04月03日
2012年4月3日
2012-04-03 10:57:06:127

代码：

--由2012-04-03 10:23:10.177转换成2012-04-03
SELECT GETDATE() AS Val,Convert(char(11),GETDATE(),120) AS NeedVal

--由2012-04-03 10:23:10.177转换成20120403
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成2012.04.03
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 102 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成04/03/2012 月日年
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 101 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成03/04/2012 日月年
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 103 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成03.04.2012 日月年
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 104 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成03-04-2012 日月年
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 105 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成03 Apr 2012 日月年
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 106 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成Apr 03, 2012
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 107 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成10:44:02
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 108 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成Apr  3 2012
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 109 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成04-03-2012
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 110 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成2012/04/03
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 111 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成20120403
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 112 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成03 Apr 2012
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 113 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成10:45:45:447
select GETDATE() AS Val,CONVERT(varchar(12) , getdate(), 114 ) AS NeedVal

--由2012-04-03 10:23:10.177转换成2012-4-3
SELECT GETDATE() AS Val,REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-') AS NeedVal

--由2012-04-03 10:23:10.177转换成2012年04月03日
SELECT GETDATE() AS Val,STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日' AS NeedVal

--由2012-04-03 10:23:10.177转换成2012年4月3日
SELECT  GETDATE() AS Val,DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月'+DATENAME(Day,GETDATE())+N'日' AS NeedVal

--由2012-04-03 10:23:10.177转换成2012-04-03 10:23:10.177
SELECT GETDATE() AS Val,CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114) AS NeedVal

2. T-SQL 日期推算

给定日期，得到该年的第一天和最后一天

给定日期所在季度的第一天和最后一天

给定日期所在月份的第一天或最后一天

给定日期所在周的取星期几的日期,星期天做为一周的第1天

昨天
前天
明天
后天
上一年

下一年

上季度

下季度
上月

下月  
上周

下周

前一小时

后一小时

前一分钟

后一分钟

前一秒

后一秒

代码：

--给定日期，得到该年的第一天和最后一天
SELECT CONVERT(char(5),GETDATE(),120)+'1-1' AS FDATE,CONVERT(char(5),GETDATE(),120)+'12-31' LDTAE

--给定日期所在季度的第一天和最后一天,两种方法算最后一天
SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE
    ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)
        +CASE WHEN DATEPART(Quarter,GETDATE()) in(1,4)
            THEN '31'ELSE '30' END) AS LDATE


SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE
    ,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+'1') AS LDATE

--给定日期所在月份的第一天和最后一天
SELECT CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1') AS FDATE,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,GETDATE()),120)+'1') AS LDATE

--给定日期所在周的取星期几的日期,星期天做为一周的第1天
SELECT DATEADD(Day,0-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,1-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,2-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,3-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,4-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,5-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,6-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())

--昨天
SELECT DATEADD(Day,-1,GETDATE())
--前天
SELECT DATEADD(Day,-2,GETDATE())
--明天
SELECT DATEADD(Day,1,GETDATE())
--后天
SELECT DATEADD(Day,2,GETDATE())

--上一年
SELECT DATEADD(year,-1,GETDATE())

--下一年
SELECT DATEADD(year,1,GETDATE())

--上季度
SELECT DATEADD(quarter,-1,GETDATE())

--下季度
SELECT DATEADD(quarter,1,GETDATE())

 --上月
SELECT DATEADD(month,-1,GETDATE())

--下月
SELECT DATEADD(month,1,GETDATE())

 --上周
SELECT DATEADD(week,-1,GETDATE())

--下周
SELECT DATEADD(week,1,GETDATE())

 --前一小时
SELECT DATEADD(hour ,-1,GETDATE())

--后一小时
SELECT DATEADD(hour ,1,GETDATE())

 --前一分钟
SELECT DATEADD(minute,-1,GETDATE())

--后一分钟
SELECT DATEADD(minute,1,GETDATE())

 --前一秒
SELECT DATEADD(second ,-1,GETDATE())

--后一秒
SELECT DATEADD(second ,1,GETDATE())

3. T-SQL日期运算

SELECT DATEDIFF(year, '2012-04-03', '2013-04-03'); --两日期差异年的运算
SELECT DATEDIFF(quarter, '2012-04-03', '2013-04-03'); --两日期差异季度的运算
SELECT DATEDIFF(day, '2012-04-03', '2013-04-03'); --两日期差异日的运算
SELECT DATEDIFF(week, '2012-04-03', '2013-04-03'); --两日期差异周的运算
SELECT DATEDIFF(hour, '2012-04-03', '2013-04-03'); --两日期差异小时的运算
SELECT DATEDIFF(minute, '2012-04-03', '2013-04-03'); --两日期差异分钟的运算
SELECT DATEDIFF(second, '2012-04-03', '2013-04-03'); --两日期差异秒的运算

DATEADD(DAY, -90, GETDATE()) ：GETDATE()获得当前时间，以day为单位，剪掉90天，就是往前数第三个月，

InDate >= DATEADD(DAY, -90, GETDATE()) ：inDate要大于剪掉90天的，就是这3个月之内的日期范围