MS SQL Server 2008:获取到接下来 8 周的一周的开始日期和结束日期 [英] MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks

问题描述

我是 SQL 的新手，任何人都可以给我查询这种情况，我需要显示从今天的日期到接下来的 8 周的一周的开始日期和结束日期".例如，如果我选择今天的日期，它应该显示

I am new to the SQL , can anyone give me query for this scenario ,"I need to display the startdate and enddate of the week starting from today's date to next 8 weeks " . For example if i select today's date , it should show

1. 开始日期 |结束日期
2. 17/03/2012 |23/03/2012
3. 24/03/2012 |29/03/2012
4. .. 等
5. 28/04/2012 |03/05/2013

推荐答案

试试这个:

DECLARE @startDate DATETIME
DECLARE @currentDate DATETIME
DECLARE @numberOfWeeks INT

DECLARE @dates TABLE(
    StartDate DateTime,
    EndDate DateTime 
)

SET @startDate = GETDATE()--'2012-01-01' -- Put whatever you want here
SET @numberOfWeeks = 8 -- Choose number of weeks here
SET @currentDate = @startDate

while @currentDate < dateadd(week, @numberOfWeeks, @startDate)
begin
    INSERT INTO @Dates(StartDate, EndDate) VALUES (@currentDate, dateadd(day, 6, @currentDate))
    set @currentDate = dateadd(day, 7, @currentDate);
end

SELECT * FROM @dates

这会给你这样的东西:

StartDate           EndDate 
21/03/2013 11:22:46 27/03/2013 11:22:46 
28/03/2013 11:22:46 03/04/2013 11:22:46 
04/04/2013 11:22:46 10/04/2013 11:22:46 
11/04/2013 11:22:46 17/04/2013 11:22:46 
18/04/2013 11:22:46 24/04/2013 11:22:46 
25/04/2013 11:22:46 01/05/2013 11:22:46 
02/05/2013 11:22:46 08/05/2013 11:22:46 
09/05/2013 11:22:46 15/05/2013 11:22:46 

或者，如果您不想要时间组件，您可以调整最终选择，如下所示:

Or you could tweak the final select if you don't want the time component, like this:

SELECT CONVERT(VARCHAR, StartDate, 103), CONVERT(VARCHAR, EndDate, 103) FROM @dates

这篇关于MS SQL Server 2008:获取到接下来 8 周的一周的开始日期和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！