How to find first and last date of week specific to month?

Question

Dear Team,

How to find first and last date of week specific to a month on passing of getdate()?

For Example:

Case 1
Today is '16 July 2010'
I want
Week_Start_Date as '11 July 2010'
and 
Week_End_Date as '17 July 2010'

Case 2
Today is '2 July 2010'
I want
Week_Start_Date as '1 July 2010'
and 
Week_End_Date as '3 July 2010'

Any help would be appreciated.

Answer 1

Hi Arup,

Try this, you might have to tinker with adding and subtracting a day to GetDate() to get exactly the range you are looking for but essentially this should work for what you want.

select convert(date, dateadd(d, -datepart(dw, getdate()), getdate())) as FirstDay
select convert(date, dateadd(d, 7-datepart(dw, getdate()), getdate())) as LastDay

Answer 2

Use a Calendar table.

SQL is a declarative language and no so great at calculations, especially temporal ones, so look for declarative, data-driven solutions.

Create an auxiliary Calendar table of dates covering all applicable dates, say twenty years into the future and a few years into the past. Add columns for week_start_date and week_end_date then populate for every day the correct values according to the algorithm in your spec. That's your SQL DDL (though I'd suggest you use a spreadsheet to create the data then import it, then apply constraints, indexes, etc).

For the SQL DML it will simply be a matter of looking up the CURRENT_TIMESTAMP (the Standard equivalent of getdate) in the Calendar table i.e. a query rather than a calculation.

Answer 3

I have written a function to help me with my requirement. I am posting the code in case anyone needs it:

Declare @FirstDayOfMonth as Datetime
Declare @LastDayOfMonth as Datetime
Declare @CurrentDate as Datetime
Declare @MinDate as Datetime
Declare @MaxDate as Datetime
Declare @MaxWeekNo as int
Declare @PresentWeekNo as int

Set @CurrentDate = '31 October 2010'

set @FirstDayOfMonth = DATEADD(month,(DATEDIFF(month,0,@CurrentDate)), 0)
set @LastDayOfMonth = dateadd(mm,1,@CurrentDate - day(@CurrentDate)+1)-1
set @MaxWeekNo = dbo.FINDINWEEK(@LastDayOfMonth)
set @PresentWeekNo = dbo.FINDINWEEK(@CurrentDate)

set @MinDate = 
Case when @PresentWeekNo = 1 then
    CAST(MONTH(@CurrentDate) AS VARCHAR(2))+'/'+'1'+'/'+ CAST(YEAR(@CurrentDate) AS VARCHAR(4))
Else
    DATEADD(dd,-(DATEPART(dw, @CurrentDate) - 1),@CurrentDate)
End

set @MaxDate = 
Case when @MaxWeekNo = 4 then
    Case when @PresentWeekNo = 4 then
        CAST(MONTH(@CurrentDate) AS VARCHAR(2))+'/'+CAST(day(@LastDayOfMonth) as VARCHAR(2))+'/'+ CAST(YEAR(@CurrentDate) AS VARCHAR(4))
    Else
        DATEADD(dd,-(DATEPART(dw, @CurrentDate) - 7),@CurrentDate)
    End
when @MaxWeekNo = 5 then
    Case when @PresentWeekNo = 5 then
        CAST(MONTH(@CurrentDate) AS VARCHAR(2))+'/'+CAST(day(@LastDayOfMonth) as VARCHAR(2))+'/'+ CAST(YEAR(@CurrentDate) AS VARCHAR(4))
    Else
        DATEADD(dd,-(DATEPART(dw, @CurrentDate) - 7),@CurrentDate)
    End
when @MaxWeekNo = 6 then
    Case when @PresentWeekNo = 6 then
        CAST(MONTH(@CurrentDate) AS VARCHAR(2))+'/'+CAST(day(@LastDayOfMonth) as VARCHAR(2))+'/'+ CAST(YEAR(@CurrentDate) AS VARCHAR(4))
    Else
        DATEADD(dd,-(DATEPART(dw, @CurrentDate) - 7),@CurrentDate)
    End
End 

Print @MinDate
Print @MaxDate