Skip to content
Advertisement

SQL Query to return last week’s data

I am trying to run a sql query that will return last week’s data to the date. For example, today is Monday. I want to return last Monday’s data only. The prior week to the day. What I am doing is getting last week’s ARAging Snapshot for my company. (What the data was last Monday, compared to this Monday)

Here is my query but, I am not sure it’s correct.

select * from ARAgingReportLine
where ReportRunDate  = DATEADD(week, - 1, GETDATE())

Advertisement

Answer

It might be that your ReportRunDate is a datetime rather than a datew. So, with times included, comparing to just the date will only shows records run at midnight.

Try casting both side of the comparison to Date datatypes, this will remove the time portion and give you the matches you want.

SELECT * 
    FROM ARAgingReportLine
    WHERE CAST(ReportRunDate AS Date) = CAST(DATEADD(week, - 1, GETDATE()), AS Date)

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement