How to find Week start date and Week end date

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  March 13, 2009 at 4:19 am

  #958974

  I think this fulfills OP's amended requirements:

  DECLARE @InputDate datetime

  DECLARE @StartDate datetime

  SELECT @InputDate = DATEADD(day, DATEDIFF(day, '17530101', '20090316 13:15'), '17530101')

  SELECT @StartDate = DATEADD(DAY, DATEDIFF(day, '17530101', @InputDate) / 7 * 7, '17530101')

  SELECT @InputDate AS InputDate,

  @StartDate AS StartDate,

  CASE WHEN (DATEDIFF(day, @StartDate, @InputDate) <= 4) THEN @InputDate

  ELSE DATEADD(day, 4, @StartDate) END AS EndDate

• johnrobin80

  Grasshopper

  Points: 13

  More actions

  July 5, 2017 at 3:44 am

  #1949417

  Current Week ==>First day of the week :
  SELECT CONVERT(VARCHAR(10),(DATEADD(DAY,-((DATEPART(DW, (DATEADD(WK, 0, GETDATE()))))-1),((DATEADD(WK,0,GETDATE()))))),120) AS [Current_Week_FirstDay]

  Current Week ==> Last day of the week
  SELECT CONVERT(VARCHAR(10),(DATEADD(DAY,7-((DATEPART(DW, (DATEADD(WK, 0, GETDATE()))))),((DATEADD(WK,0,GETDATE()))))),120) AS [Current_Week_LastDay]

  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  Last Week ==> First day of the week :
  SELECT CONVERT(VARCHAR(10),(DATEADD(DAY,-((DATEPART(DW, (DATEADD(WK, -1, GETDATE()))))-1),((DATEADD(WK,-1,GETDATE()))))),120) AS [Last_Week_FirstDay]

  Last Week ==> Last day of the week :
  SELECT CONVERT(VARCHAR(10),(DATEADD(DAY,7-((DATEPART(DW, (DATEADD(WK, -1, GETDATE()))))),((DATEADD(WK,-1,GETDATE()))))),120) AS [Last_Week_LastDay]

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  July 5, 2017 at 6:55 am

  #1949456

  karthik M - Tuesday, March 3, 2009 3:37 AM

  Hi All,Thanks for all your inputs!:)I found the solution.Declare @InputDate Datetimeselect @InputDate = '02/mar/2009'select case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-7,@InputDate) when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate) when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate) when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate) when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate) when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate) when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate) END as StartDate, case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-3,@InputDate) when upper(datename(dw,@InputDate)) in('TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then @InputDate when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate) when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate) END as EndDate It worked well for all the cases.

  Such complexity is not necessary.  Please tell us what day of the week a week starts with.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)