Getting the Starting and ending date of week? In Sql server?

Question

In sql, how to get the starting and ending date of week for the particular month. Can you help me.

Answer 1

The following will work whatever you consider the first day of the week (sunday, monday etc), just ensure you use SET DATEFIRST if you want to change from the default. SET DATEFIRST 1 will make the first day of the week monday.

SELECT  DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekStart],
        DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [WeekEnd]

---

EDIT

I have just re-read you request and I think you may be after something different to what I have given above. If you want the day of the week of the first and last of the month this will do the trick:

SELECT  DATENAME(WEEKDAY, DATEADD(DAY, 1 - DATEPART(DAY, GETDATE()), GETDATE())) [FirstDayOfMonth],
        DATENAME(WEEKDAY, DATEADD(DAY, - DATEPART(DAY, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE()))) [LastDayOfMonth]

Answer 2

To get first day of week use:

select dateadd(wk, datediff(wk, 0, getdate()), 0)

Last day will be first day + 6:

select dateadd(wk, datediff(wk, 0, getdate()), 0) + 6

Warning: this is culture sensitive. Check documentation on @@datefirst

Answer 3

Your requirement is unclear: what do you consider to be the first and last days of a week? Sunday and Saturday? Monday and Friday? Monday and Sunday? But the best solution for many date-related queries is to create a calendar table. That gives you the ability to easily set the first and last days of weeks, months and years according to your definition and even in multiple formats if necessary.

This is especially useful if you work with concepts like financial years, business days, and so on where the definitions vary by country and even company. It is also the easiest solution when there are exceptions to your logic, e.g. the first week of the year has different rules than a 'normal' week.

With a calendar table you could pre-populate the first and last days of the week and then your query could be something like this:

-- first/last days stored as dates in their own columns
select FirstDayOfThisWeek, LastDayOfThisWeek
from dbo.Calendar
where BaseDate = @SomeDate

-- first/last days stored as bit flags
select max(BaseDate)
from dbo.Calendar
where BaseDate <= @SomeDate and IsFirstDayOfWeek = 0x1