从该月的指定星期开始获取开始日期和结束日期

Question

我需要从指定月份的（1/2/3/4/5）星期开始查找开始日期和结束日期。以便我可以在指定的（1/2/3/4/5）周内获取Date_Created所在的记录。

例如，如果

I choose 1st week of febuary 2013 then 
I want startdate = 2/1/2013 and enddate = 2/2/2013 
I choose 2nd week of febuary 2013 then 
I want startdate = 2/3/2013 and enddate = 2/9/2013 

和与之相似休息周。

我在数据库端有SQL Server 2008。如果有人有想法，那么请分享。

Answer 1

下面是一个查询来获取所有周的开始和结束日期给定月份。所以，你可以从该查询本周选择您需要：

SQLFiddle example

with C(i) as 
(select CAST('2013-02-01' as datetime) i 
    UNION ALL 
    select DATEADD (day,1,i) i from C 
    where DATEADD (day,1,i) 
      <DATEADD(month,1,'2013-02-01') 
), C1 as 
(
select DATEPART(WEEK,i)-DATEPART(WEEK,'2013-02-01')+1 WeekOfMonth,i from C 
) 
select WeekOfMonth,min(i) StartDate, max(i) EndDate from C1 group by WeekOfMonth 

Answer 2

您可以尝试此操作以获取本周的开始或结束日期。只需提供您找到日期的日期。

逻辑非常简单，就是获取一周的开始日期和结束日期，然后检查开始日期是否大于月份日期，如果是以月份日期作为星期开始日期，或者是一周的开始日期。

拨弄了一下得到你想要 这里是什么代码：

DECLARE @date datetime 
SET @date = '2013-01-30' 
DECLARE @startdate datetime 
DECLARE @enddate datetime 
DECLARE @MonthStart datetime 
Declare @MonthEnd datetime 

SET @startdate = DATEADD(wk, DATEDIFF(wk, 6, @date), 6) 
SET @enddate = DATEADD(dd,6, DATEADD(wk, DATEDIFF(wk, 6, @date), 6)) 
SET @monthStart = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),101) 
SET @MonthEnd =CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),DATEADD(mm,1,@date)),101) 

--select @startdate wstart,@MonthStart monthstart,@enddate wend,@MonthEnd monthend 
select 
[date] = @date 
,[week] = DATEPART(wk,@date) 
,[WeekStartDate] = Case 
         WHEN @startdate <= @MonthStart then @MonthStart else @startdate 
        END 
,[WeekEndDate] = Case 
         WHEN @enddate > @MonthEnd then @MonthEnd else @enddate 
        END 

在这里看到：

Fiddle

Answer 3

我写了一个程序来解决我的问题，这将需要一周没有（1/2/3/4/5）作为输入和将返回开始日期和结束日期

ALTER PROCEDURE dbo.SPGetStartAndEndDateofSpcifiedWeek 
    @Week int 
AS 
    SET NOCOUNT ON 
    DECLARE @date DateTime 
    DECLARE @currdate DateTime 
    DECLARE @startdate DateTime 
    DECLARE @enddate DateTime 
    DECLARE @CurrWeek int 

    /*SET @date = CONVERT(date,GETDATE())*/ 
    SET @currdate = CONVERT(date,GETDATE()) 
    SET @CurrWeek = datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, GETDATE()), 0)), 0), GETDATE() - 1) + 1 

    IF (@Week = 1) 
    BEGIN 
     IF (@CurrWeek = 1) 
     BEGIN SET @date = @currdate END 
     IF (@CurrWeek = 2) 
     BEGIN SET @date = @currdate - 7 END 
     IF (@CurrWeek = 3) 
     BEGIN SET @date = @currdate - 14 END 
     IF (@CurrWeek = 4) 
     BEGIN SET @date = @currdate - 21 END 
     IF (@CurrWeek = 5) 
     BEGIN SET @date = @currdate - 28 END 
    END 
    IF (@Week = 2) 
    BEGIN 
     IF (@CurrWeek = 1) 
     BEGIN SET @date = @currdate + 7 END 
     IF (@CurrWeek = 2) 
     BEGIN SET @date = @currdate END 
     IF (@CurrWeek = 3) 
     BEGIN SET @date = @currdate - 7 END 
     IF (@CurrWeek = 4) 
     BEGIN SET @date = @currdate - 14 END 
     IF (@CurrWeek = 5) 
     BEGIN SET @date = @currdate - 21 END 
    END 
    IF (@Week = 3) 
    BEGIN 
     IF (@CurrWeek = 1) 
     BEGIN SET @date = @currdate + 14 END 
     IF (@CurrWeek = 2) 
     BEGIN SET @date = @currdate + 7 END 
     IF (@CurrWeek = 3) 
     BEGIN SET @date = @currdate END 
     IF (@CurrWeek = 4) 
     BEGIN SET @date = @currdate - 7 END 
     IF (@CurrWeek = 5) 
     BEGIN SET @date = @currdate - 14 END 
    END 
    IF (@Week = 4) 
    BEGIN 
     IF (@CurrWeek = 1) 
     BEGIN SET @date = @currdate + 21 END 
     IF (@CurrWeek = 2) 
     BEGIN SET @date = @currdate + 14 END 
     IF (@CurrWeek = 3) 
     BEGIN SET @date = @currdate + 7 END 
     IF (@CurrWeek = 4) 
     BEGIN SET @date = @currdate END 
     IF (@CurrWeek = 5) 
     BEGIN SET @date = @currdate - 7 END 
    END 
    IF (@Week = 5) 
    BEGIN 
     IF (@CurrWeek = 1) 
     BEGIN SET @date = @currdate + 28 END 
     IF (@CurrWeek = 2) 
     BEGIN SET @date = @currdate + 21 END 
     IF (@CurrWeek = 3) 
     BEGIN SET @date = @currdate + 14 END 
     IF (@CurrWeek = 4) 
     BEGIN SET @date = @currdate + 7 END 
     IF (@CurrWeek = 5) 
     BEGIN SET @date = @currdate END 
    END 

    Select CONVERT(date, DATEADD(wk, DATEDIFF(wk, 0, @date), - 1)) as startdate, 
       CONVERT(date, DATEADD(wk, DATEDIFF(wk, 0, @date), 5)) as enddate 

    RETURN 

Answer 4

我修改了Valex给出的代码，以获得我需要的确切输出。

CREATE PROCEDURE dbo.SPReturnStartEndDateOfSpecifiedWeek 
    @Week int, 
    @P_startdate DateTime OUTPUT, 
    @P_enddate DateTime OUTPUT 
AS 
    /* SET NOCOUNT ON */ 

    with C(i) as 
    ( 
     select CAST((DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) as datetime) i 
     UNION ALL 
     select DATEADD (day,1,i) i from C 
     where DATEADD (day,1,i)<DATEADD(month,1,(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) 
    ), 
    C1 as 
    (
     select DATEPART(WEEK,i)-DATEPART(WEEK,(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))+1 WeekOfMonth,i from C 
    ), 
    C2 as 
    (
     select WeekOfMonth,min(i) StartDate, max(i) EndDate from C1 group by WeekOfMonth 
    ) 

    Select @P_startdate = StartDate, 
      @P_enddate = EndDate 
    from C2 
    WHERE [email protected] 

RETURN