http://blog.csdn.net/dba_huangzj/article/details/7657979
01.--定义给定的一天
02.DECLARE @Date DATETIME = GETDATE();
03.
04.SELECT @Date AS '目前时间'
05.,DATEADD(DD,-1,@Date) AS '前一天'
06.,DATEADD(DD,1,@Date) AS '后一天'
07./*月计算*/
08.,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算，保证日恒久为1号
09.,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天，建议使用DATEADD而不要直接“-1”
10.,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
11.,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
12.,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
13.,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
14.,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
15./*周计算*/
16.,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
17.,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
18.,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
19.,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
20.,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
21.,DATENAME(WEEKDAY,@Date) AS '本日是周几'
22.,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日，2-星期一，3-星期二......7-星期六
23./*年度计算*/
24.,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
25.,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
26.,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
27.,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
28.,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
29.,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
30./*季度计算*/
31.,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
32.,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
33.,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
34.,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
35.,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
36.,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
