7

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

Surya sasidhar
  • 29,607
  • 57
  • 139
  • 219

4 Answers4

9

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]
GarethD
  • 68,045
  • 10
  • 83
  • 123
1

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
Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51
1

Below is a case statement you can use to create a Weekbegin for your date value. It will make your weeks start on Monday or Tuesday or Wed... etc. dependant on what day of the week you have set as @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 your select

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, just by using dateadd(d, 6, THE CASE STATEMENT)

BartoszKP
  • 34,786
  • 15
  • 102
  • 130
WalkingLoreSuit
  • 23
  • 5
0

Here i given

Week,month,quarter,half year,year start and end date.

     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
Ratha Krishnan
  • 11
  • 4