如何获取两个日期之间的周开始日期(星期一)和结束日期(星期日)的列表?

问题描述

我只是想找到一种方法来获取两个日期范围之间的开始周和结束周的列表.

例如如果我调用函数或存储过程，请说

GetWeekDates('2014 年 3 月 21 日'、'2014 年 3 月 21 日')

然后我应该得到一张桌子:

开始一周 |结束周2014 年 3 月 17 日 |2014 年 3 月 23 日2014 年 3 月 24 日 |2014 年 3 月 30 日....

我不想使用 CURSOR 或 WHILE 循环.

有什么我应该去的方向吗?

我只是对现有代码进行重构.

亲切的问候，上午

解决方案

只要间隔为 38 年或更短，这将起作用.它将提供更好的性能，并且不依赖于服务器的本地设置.

这个设置会导致你的脚本返回错误的结果:

set datefirst 1select * from dbo.fnGetWeeksBetweenDates('2014-03-21','2014-03-21')

这意味着您的本地设置当前与您的需求冲突，您的代码正在补偿.

这是脚本.出于性能原因，脚本被限制为 38 年(我发现您不太可能需要比这更大的间隔).将其延长到更多年将相当容易.

DECLARE @FromDate DATE = '2014-03-21'声明 @ToDate 日期 = '2014-03-24'SELECT @fromdate = dateadd(day, datediff(day, 0, @FromDate)/7*7, 0),@todate = dateadd(day, datediff(day, 0, @ToDate)/7*7, 6)SELECT dateadd(d, number * 7, @fromdate) Start_Week,dateadd(d, number * 7 + 6, @fromdate) End_Week从master..spt_valuesWHERE 类型 = 'P' 和@todate >= dateadd(d, number * 7, @fromdate)

结果:

Start_Week End_Week2014-03-17 2014-03-232014-03-24 2014-03-30

I am just finding a way to get a list of Start Week and End Week between two date range.

For e.g. if I call a function or a stored procedure say

GetWeekDates ('21 Mar 2014','21 Mar 2014')

Then I should get a table as:

Start Week | End Week

17 March 2014 | 23 Mar 2014
24 March 2014 | 30 Mar 2014
....

Any direction where I should lead to?

I am just doing a re factor on existing code.

Kind regards,Am

解决方案

This will work as long as the intervals are 38 years or less. It will offer better performance and it does not rely on the local setting of the server.

This setting will cause your script to return wrong result:

set datefirst 1
select * from dbo.fnGetWeeksBetweenDates('2014-03-21','2014-03-21')

This means your local setting currently conflicts with your needs and your code is compensating.

Here is the script. The script is limited to 38 years for performance reasons (I find it unlikely that you need bigger intervals than that). It will be fairly easy to extend it to more years.

DECLARE @FromDate DATE = '2014-03-21'
DECLARE @ToDate DATE  = '2014-03-24'

SELECT @fromdate = dateadd(day, datediff(day, 0, @FromDate)/7*7, 0),
@todate = dateadd(day, datediff(day, 0, @ToDate)/7*7, 6)

SELECT dateadd(d, number * 7, @fromdate) Start_Week,
dateadd(d, number * 7 + 6, @fromdate) End_Week
FROM
master..spt_values
WHERE type = 'P' and
@todate >= dateadd(d, number * 7, @fromdate)

Result:

Start_Week  End_Week
2014-03-17  2014-03-23
2014-03-24  2014-03-30

这篇关于如何获取两个日期之间的周开始日期(星期一)和结束日期(星期日)的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！