Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1536
  • Last Modified:

how do I get pull data from last Monday to This past Sunday in a MS SQL

I have a query that pulls data but the client wants it weekly every Tuesday, but he wants it to show from Monday to Sunday of the week before.  I have seen the set datefirst function but i am just not sure how to incoportate it into my query.  So for example next Tuesday is
Feb 25 so on that day they would like to see the data from  Feb 17(Mon) to Feb 23(Sun).

Here is my query, notice what I put in my in the where  clause, this doesnt give me last week only this week.

Select  c.episode_no as Pt_No,
           c.pt_id_start_dtime as Adm_Date,
           c.rpt_name as Pt_Name,
           convert(varchar(10),c.birth_dtime,101) as 'DOB',
           o.orgz_short_name as 'Entity',
           c.cre_dtime as Case_Date,
           a.last_name+', ' + a.first_name as Guarantor_Name,
        from smsmir.mir_pms_case as c
  inner join smsmir.mir_pers_addr as a
     on   c.src_sys_id = a.src_sys_id
     and c.pt_id = a.pt_id
     and c.episode_no = a.episode_no
     and c.pt_id_start_dtime = a.pt_id_start_dtime    
   inner join smsmir.mir_vst_pms as v
     on   c.src_sys_id = v.src_sys_id
     and c.pt_id = v.pt_id
     and c.episode_no = v.episode_no
     and c.pt_id_start_dtime = v.pt_id_start_dtime
where c.pt_type like 'E%'
    and c.pt_id_start_dtime between  DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0)      and DATEADD(week, DATEDIFF(day, 0, getdate())/7, 5)
  • 2
1 Solution
You can not incorporate DateFirst into the query.

It is a setting for the server which you can change for your session.

If you need the last sundaythen you should set your DateFirst to Saturday (which means 7 and is the default)


SET @@DateFirst = 7

Then you can do something like

SELECT DateAdd(day, ((datepart(dw,GetDate())-1) * -1), GetDate())

which will always return the last sunday (except if you excute it on a sunday).

  SELECT DateAdd(day, ((datepart(dw,GetDate())-1) * -1)-6 , GetDate())

will return the monday before the last sunday.

where c.pt_type like 'E%'
    and c.pt_id_start_dtime between  DateAdd(day, ((datepart(dw,GetDate())-1) * -1)-6 , GetDate())      and DateAdd(day, ((datepart(dw,GetDate())-1) * -1), GetDate())

should do the trick for you.
Sorry.. in line 3 it must read:

If you need the last sundaythen you should set your DateFirst to Sunday (which means 7 and is the default)...

I did the thing first for Saturday because I misread the requirements .-)

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now