Getting the start and end dates of the week? On the Sql server?

Question

In sql, how to get the start and end date of the week for a specific month. Can you help me.

Answer 1

The following will work no matter what you think on the first day of the week (Sunday, Monday, etc.), just make sure you use SET DATEFIRST if you want to change the default value. SET DATEFIRST 1 will make the first day of the week of 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 just re-read your request, and I think you can be something other than what I said above. If you want the day of the week of the first and last 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 use the first day of the week:

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

The last day will be the first day + 6:

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

Warning: it is culture sensitive. Check the documentation at @@ datefirst

Answer 3

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

This is especially useful if you work with concepts such as fiscal years, business days, etc., where the definitions depend on the country and even the company. This is also the easiest solution when there are exceptions to your logic, for example. The first week of the year has different rules than the "normal" week.

With a calendar table, you can pre-populate the first and last days of the week, and then your request may look 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 

Answer 4

The following is an example of a case that you can use to create a Weekbegin for your date value. This will make your weeks begin on Monday or Tuesday or Wed. Etc. Depending on what day of the week you set @pDate.

Create Parameters

 DECLARE @pDate Date = NULL --Replace with your date, which will also be the first day of the week DECLARE @pDatePart SMALLINT = DATEPART(dw, @pDate) 

Then put this case statement after selecting

 CASE WHEN DATEPART(dw, CAST(DATEVALUE AS DATE)) BETWEEN @pDatePart AND 7 THEN DATEADD(d, (DATEPART(dw, CAST(DATEVALUE AS DATE))*-1) +@pDatePart , CAST(DATEVALUE AS DATE)) WHEN DATEPART(dw, CAST(DATEVALUE AS DATE)) BETWEEN 1 AND @pDatePart-1 THEN DATEADD(d, (DATEPART(dw, CAST(DATEVALUE AS DATE))*-1)+(@pDatePart-7), CAST(DATEVALUE AS DATE)) END AS DynamicWeekBegin 

You can always get the final date of the week simply by using dateadd (d, 6, STATEMENT OF BUSINESS)

Answer 5

Here i gave

Week, month, quarter, six months, year of beginning and end.

  Select CONVERT(varchar(50), GETDATE(),105) 'GETDATE' , CONVERT(varchar(50), DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)),105) [WeekStart], CONVERT(varchar(50),DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) ,105)[WeekEnd], CONVERT(varchar(50),DATEADD(dd, -DAY(getdate()) + 1, getdate()),105) MonthStart, CONVERT(varchar(50),DATEADD(dd, -DAY(DATEADD(mm, 1, getdate())), DATEADD(mm, 1, getdate())),105) MonthStart, CONVERT(varchar(50), DATEADD(q, DATEDIFF(q, 0, GETDATE()), 0),105) AS 'QStart Date', CONVERT(varchar(50), DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, GETDATE()) + 1,0)),105) AS 'QEnd Date', CONVERT(varchar(50), CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 1) AS VARCHAR) + '-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME),105) StartOfHalfYear, CONVERT(varchar(50), CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 6) AS VARCHAR) + '-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME),105)EndOfHalfYear, CONVERT(varchar(50), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),105) AS StartOfYear, CONVERT(varchar(50), DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1),105) AS EndOfYear