CODEWITHSUNDEEP
sqlsql-serverssms
Si8
Si8

Reputation: 9225

How to get the date from last Sunday to Saturday

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?

Upvotes: 1

Views: 3054

Answers (1)

Joseph B
Joseph B

Reputation: 5669

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

Upvotes: 2

Related Questions