How to get the date from last Sunday to Saturday

Question

I will entries in my table which will have date with data like this:

Date          Value
4-20-2014     45
4-21-2014     15
4-22-2014     35
...
4-30-2014     109

I will be executing my Stored Procedure every Sunday which means I would like to get the row with Date from previous Sunday until Yesterday (Saturday).

I have the following query which will get from Previous Sunday to Previous Saturday which I think is wrong so I modified it as the following:

INSERT INTO [database].[dbo].[table]
SELECT (WEEKENDING DATE) AS [Date], SUM([ORDERS]) AS Orders, SUM([B ORDERS]) AS bOrders, SUM([RESULT]) AS Results
FROM [database].[dbo].[origtable]
WHERE
    [Date] >= "PREVIOUS SUNDAY DATE"
    AND
    [Date] <= "SATURDAY DATE (YESTERDAY)"

I would like to get some help with the following line:

    [Date] >= "PREVIOUS SUNDAY DATE"
    AND
    [Date] <= "SATURDAY DATE (YESTERDAY)"

So if I run the SP on 5/4/2014, it will grab between Sunday (4/27/2014) to Saturday (5/3/2014)

Will this work:

INSERT INTO [Database].[dbo].[table]
SELECT 
    CONVERT(VARCHAR(10), GETDATE(), 101) AS [Date], 
    SUM([EMR ORDERS]) AS LastWeekEMROrders, 
    SUM([ACCESSIONED LAB ORDERS]) AS LastWeekAccLabOrders, 
    SUM([LAB_RESULT]) AS LastWeekLabResults
FROM [database].[dbo].[origtable]
WHERE
    [Date] >= DATEADD(day, -((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7) - 6,
              DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SUNDAY
    AND
    [Date] <= DATEADD(day, -(DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7,
              DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SATURDAY

If I have the following query and execute it on 5/4/2014:

set @startdate = DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2) --for sunday
set @enddate = DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk, 0,getdate()), -1))-- for saturday

What will be the sunday's and saturday's date?

Yes, the last seven days. I added what I have so far at the bottom of my question and please let me know if it will work. — Si8, Apr 30 '14 at 15:14
Have you tried to execute the select (without the insert) to see what happen — Serpiton, Apr 30 '14 at 15:31
It is currently showing last Sunday's date which is 4/27/ But I will wait until Monday to see which date it took. — Si8, Apr 30 '14 at 15:43
Does your [Date] column only have date information? No datetime? — sam yi, Apr 30 '14 at 16:36
To get data from last Saturday to Sunday refer to my answer here: https://stackoverflow.com/a/67581649/2462531 — Shailendra Madda, May 18 '21 at 07:25

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

Try the following modified query:

EDIT: The query has been further modified to not use DATEFROMPARTS.

INSERT INTO [Database].[dbo].[table]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [Date], SUM([EMR ORDERS]) AS LastWeekEMROrders, SUM([ACCESSIONED LAB ORDERS]) AS LastWeekAccLabOrders, SUM([LAB_RESULT]) AS LastWeekLabResults
FROM [database].[dbo].[origtable]
WHERE
    [Date] >= DATEADD(d, -8, DATEADD(dd, DATEDIFF(dd,0, current_timestamp), 0)) --PREVIOUS SUNDAY
AND
    [Date] <= DATEADD(d, -1, DATEADD(dd, DATEDIFF(dd,0, current_timestamp), 0)) --PREVIOUS SATURDAY;

References:

Date and Time Data Types and Functions (Transact-SQL) on TechNet

Related SO question

edited May 23 '17 at 10:32

Community

1
1

answered Apr 30 '14 at 15:31

Joseph B

5,519
1
15
19

UPVOTED but `datefromparts` is not recognized. I am using SQL server 2012 – Si8 Apr 30 '14 at 15:43
What error do you get? Maybe the syntax/parentheses is not correct? http://technet.microsoft.com/en-us/library/hh213228.aspx – Joseph B Apr 30 '14 at 15:51
Thanks, I did check the article but the datefromparts is not a recognized function. – Si8 Apr 30 '14 at 16:07
Do you get an error? What happens when you run just the SELECT statement? – Joseph B Apr 30 '14 at 16:10
`Msg 195, Level 15, State 10, Line 4 'datefromparts' is not a recognized built-in function name.` – Si8 Apr 30 '14 at 16:15
The DATEFROMPARTS function was introduced in SQL server 2012. Since the function does not work for you, an alternative must be used. – Joseph B Apr 30 '14 at 16:18
I am running SQL Server 2012 :/ – Si8 Apr 30 '14 at 16:23
1

Please see the modified version of the query above. Actually, the condition reads even simpler now. – Joseph B Apr 30 '14 at 16:26
Thank you. I will update this and accept your answer as soon as I am able to start testing it. – Si8 Apr 30 '14 at 16:33

How to get the date from last Sunday to Saturday

1 Answers1

Linked