日期推算处理

 日期推算处理

对于日期查询来说，多数都是查询本周、本月、本年这类没有固定的开始和结束日期的日期段的数据，所以根据一个指定的日期，推算出与之相关的各日期，是日期处理中经常要遇到的问题。

1．  指定日期该年的第一天或最后一天

（1）分析

对于年的第一天或最后一天，它们的月日信息（第一天为1月1日、最后一天为12月31日）都是固定的，所以只需取出指定日期的年份，再加上月份和天（字符串相加）就可以了。

（2）处理代码

¡  年的第一天

CONVERT(char(5),<date>,120)+'1-1'

¡  年的最后一天

CONVERT(char(5),<date>,120)+'12-31'

2．  指定日期所在季度的第一天或最后一天

（1）分析

首先分析月份，可以用DATEPART（Quarter,date）函数取得指定日期所在的季度，一个季度有3个月份，所以DATEPART（Quarter,date）*3就是该季度的最后一个月的月份，再减去2就是该季度最早一个月的月份。接下来就要把指定日期的月份转换到这个推算出的月份，这个可以把指定日期减去指定日期的月份数，得到指定日期所在年的上一年的最后一个月，然后再加上由季度推算出来的月份数。

再分析天的处理，对于第一天，可以直接取得换算后的年月信息字符串，再用字符串相加上天的信息，转换回日期型就是指定日期所在季度的第一天；对于最后一天，由于一年就4个季度，对应的每季度的最后一个月分别是3、6、9、12这4个月，它们的最后一天是确定的，分别是31、30、30、31，所以完全可以用CASE来判断处理。另一种方法是用所在季度最后一个月的下一个月的第一天减1天。

（2）处理代码

¡  季度的第一天

CONVERT(datetime,

CONVERT(char(8),

           DATEADD(Month,

                    DATEPART(Quarter,<date>)*3-2,

                    DATEADD(Month,-Month(<date>),<date>)),

           120)+'1')

¡  季度的最后一天（CASE判断法）

CONVERT(datetime,

CONVERT(char(8),

           DATEADD(Month,

                    DATEPART(Quarter,<date>)*3,

                    DATEADD(Month,-Month(<date>),<date>)),

           120)

+CASE WHEN DATEPART(Quarter,<date>) in(1,4)

           THEN '31' ELSE '30' END)

¡  季度的最后一天（直接推算法）

DATEADD(Day,-1,

CONVERT(char(8),

           DATEADD(Month,

                    DATEPART(Quarter,<date>)*3+1,

                    DATEADD(Month,-Month(<date>),<date>)),

           120)+'1')

可以对上述代码进行简化，合并推算的处理步骤，下面是简化后的示例代码。

¡  季度的第一天

CONVERT(datetime,

CONVERT(char(8),

           DATEADD(Month,

                    DATEPART(Quarter,<date>)*3-Month(<date>)-2,

                    <date>),

           120)+'1')

¡  季度的最后一天（CASE判断法）

CONVERT(datetime,

CONVERT(char(8),

           DATEADD(Month,

                    DATEPART(Quarter,<date>)*3-Month(<date>),

                    <date>),

           120)

+CASE WHEN DATEPART(Quarter,<date>) in(1,4)

           THEN '31'ELSE '30' END)

¡  季度的最后一天（直接推算法）

DATEADD(Day,-1,

CONVERT(char(8),

           DATEADD(Month,

                    1+DATEPART(Quarter,<date>)*3-Month(<date>),

                    <date>),

           120)+'1')

3．  指定日期所在月份的第一天或最后一天

（1）分析

所在月份的第一天固定为1，只需要取出指定日期的年月部分再加上1就行了。对于月份的最后一天，它随月份不同而不同，而且还会受平年与闰年的影响，不过，当前月份的最后一天肯定是它的下个月的1号减去1天，而下个月的1号很容易确定，所以只需要取得指定日期的下个月1号的日期，然后减1天就行了。

在推算日期所在月份最后一天的处理中，一个容易犯的错误是：将指定日期减去当前日期的天数，得到指定日期的上一个月的最后一天，然后将这个日期加上1个月来得到指定日期所在月份的最后一天。如果使用这种处理方法，当指定日期上个月的天数比指定日期所在月份的天数多时，不会出现问题。否则就会少计算天数。

（2）处理代码

¡  月的第一天

CONVERT(datetime,CONVERT(char(8),<date>,120)+'1')

¡  月的最后一天

DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,<date>),120)+'1')

¡  月的最后一天（容易使用的错误方法）

DATEADD(Month,1,DATEADD(Day,-DAY(<date>),<date>))

4．  指定日期所在周的任意一天

（1）分析

一周固定为7天，可以通过DATEPART（Weekday,date）函数得到指定日期是该周的第几天，将指定日期减去这个天数，那么就是所在星期上一周的最后一天，然后加上要得到的所在星期的天数，那么就可以得到指定日期所在周的任意一天了。

（2）处理代码

其中，<number>是周的第几天，允许的值为1～7：

DATEADD(Day,<number>,DATEADD(Day,-DATEPART(Weekday,<date>),<date>))

简化后的代码：

DATEADD(Day,<number>-DATEPART(Weekday,<date>),<date>)

5．  指定日期所在周的任意星期几

（1）分析

这个要求与推算指定日期所在周的任意一天的处理有所不同，推算指定日期所在周的任意一天严格按照SET DATEFIRST设置的一周的第一天是星期几来推算。例如，根据指定日期推算一周的第2天，如果SET DATEFIRST 7，推算出的第一天是星期一；如果，SET DATEFIRST 1，那么推算出的将是星期二。

推算出指定日期所在的周的星期几，推算的结果不受SET DATEFIRST设置的影响，即无论SET DATEFIRST的设置如何，推算指定日期的星期二，推算出的结果应该是固定的。

要推算出指定日期所在周的任意星期几，首先，要把指定日期转换为指定日期所在周的星期日（如果是按中国的日期处理习惯，则是转换为指定日期所在周的星期一）。在SQL Server中，没有得到指定日期为星期几这个数字的函数，要得到指定日期为星期几这个数字，可以使用DATEPART（Weekday,date）函数，配合系统变量@@DATEFIRST来获取。在SET DATEFIRST 1的时候，星期几和DATEPART（Weekday,Date）得到的结果是一致的，所以要避免推算出的日期受SET DATEFIRST设置的影响，首先就要将DATEPART（Weekday,date）的结果换算为SET DATEFIRST 1时的DATEPART（Weekday ,date）值：当SET DATEFIRST 2时，DATEPART（Weekday,date）把星期二放在了一周的第一天的位置，星期一被向左移了一位，所以DATEPART（Weekday,date）的结果再加上1就可以把DATEPART（Weekday,date）的结果换算为SET DATEFIRST 1时的结果，同理SET DATEFIRST 3时，只需要把DATEPART（Weekday,date）的结果加上2，就可以把结果换算为SET DATEFIRST 1时的DATEPART（Weekday,date）值，而通过@@DATEFIRST可以得到当前SET DATEFIRST设置的值，所以通过DATEADD（Day,DATEPART（Weekday,date)+@@DATEFIRST,date)可以推算出指定日期前一周的最后一个星期日，然后再加上要得到的星期几的天数，就是所要的结果。

在这个推算赛程中，还要处理一个问题，一个星期是7天，DATEPART（Weekday，date）的结果始终是1～7，所以SET DATEFIRST n，并不是把星期n之前的星期向左移，而是做循环移动，所以还要做补位处理，处理方法是把DATEPART（Weekday,date)+@@DATEFIRST-1的结果MOD 7，这样便得到了指定日期的星期几这个数字。

（2）处理代码

其中，<number>是星期几，允许的值为0～6，0是日期日，1～6为星期一～星期六

DATEADD(Day,<number>,

DATEADD(Day,-(DATEPART(Weekday,<date>)+@@DATEFIRST-1)%7,

<date>))

简化后的处理代码：

DATEADD(Day,<number>-(DATEPART(Weekday,<date>)+@@DATEFIRST-1)%7,<date>)

如果是按照东方习惯进行星期几的日期推算，则一周是以星期一开始，以星期日结束，所以在处理上，要避免MOD处理时，要避免把星期日放在前面，所以需要修改MOD的算法为：DATEPART（Weekday,date)+@@DATEFIRST-2的结果MOD 7+1。

按东方习惯进行日期推算的代码。其中，<number>是星期几，允许的值为1～7，7是日期日，1～6为星期一到星期六）：

DATEADD(Day,<number>-(DATEPART(Weekday,<date>)+@@DATEFIRST-2)%7-1,<date>)