如何在SQL中指定日期范围之间的工作日期

问题描述

我希望SQL中指定日期范围包含日期范围之间的周日期。喜欢



如果，@ Start ='2014-05-01 00：00：00.000'和@End ='2014-05-31 00：00：00.000'日期是这个

I want week dates between specified date range inclusive date range in SQL. Like

If, @Start = '2014-05-01 00:00:00.000' and @End = '2014-05-31 00:00:00.000' Dates are this

1   2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2   2014-05-07 00:00:00.000 2014-05-14 00:00:00.000
3   2014-05-14 00:00:00.000 2014-05-21 00:00:00.000
4   2014-05-21 00:00:00.000 2014-05-28 00:00:00.000
5   2014-05-28 00:00:00.000 2014-05-31 00:00:00.000

如果，@ Start ='2014-05-01 00：00：00.000'和@End ='2014-05-1800：00：00.000'日期是这个

If, @Start = '2014-05-01 00:00:00.000' and @End = '2014-05-1800:00:00.000' Dates are this

1   2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2   2014-05-07 00:00:00.000 2014-05-14 00:00:00.000
3   2014-05-14 00:00:00.000 2014-05-18 00:00:00.000

先谢谢

Thanks in Advance

推荐答案

declare @fromDate datetime,@toDate datetime
set @fromDate='12 Jun 2014'
set @toDate='28 Jun 2014'


 ;WITH CTEQuery AS (
			  SELECT CAST(@fromDate AS DATETIME) AS FirstDay, DATEADD(dd, 7, @fromDate) as LastDay
			  UNION ALL
			  SELECT DATEADD(dd, 7, FirstDay), DATEADD(dd, 7, LastDay)
			   FROM CTEQuery s
			   WHERE DATEADD(dd, 7, FirstDay) <= CAST(@toDate AS DATETIME)
			   )
select * from CTEQuery 

你可以传递 @fromDate 和 @toDate 动态在这里我已经传递了它，例如.. :)）

you can pass @fromDate and @toDate dynamically here i have passed it for example.. :)

这篇关于如何在SQL中指定日期范围之间的工作日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！