CODEWITHSUNDEEP
sqlsql-serverdate
SaxChick
SaxChick

Reputation: 13

Need help extracting SQL data for 2 previous days

I need to run a SQL script every night that extracts data from the previous 2 days. For example: On July 9 at 1am, the script runs and needs to extract data from July 8 and July 7. On July 10 at 1am, it needs to extract data from July 9 and July 8, etc.

The script is functional, in that it correctly extracts data for a fixed date range (by including the actual date range in the script), but I don't know how to make it do the "2 days prior" part.

Figuring this out is beyond me! Can anyone provide guidance?

Using SQL Server 2014

Upvotes: 1

Views: 381

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can do:

where datecol >= convert(date, dateadd(day, -2, getdate())) and
      datecol < convert(date, getdate())

That said, I would be very wary about putting this logic directly into a query. I would create a stored procedure in SQL Server and have it take @fromdate and @todate arguments.

Then, schedule a job that does the above calculation and calls the stored procedure with the right parameters.

One day, when the server is down or the logic fails, you will appreciate having the flexibility to specify the date range yourself.

Upvotes: 1

Airn5475
Airn5475

Reputation: 2492

I would create three variables.

  • @today: is the current datetime cast to a date to set it to midnight
  • @startDate: first/start date where I would use the DATEADD function to subtract two days
  • @endDate: end date that you can subtract 1 second from today

This should get you a date range of 2019-07-07 00:00:00.000 to 2019-07-08 23:59:59.000

DECLARE @today DATETIME = CAST(GETDATE() AS DATE);
DECLARE @startDate DATETIME = DATEADD(DAY, -2, @today);
DECLARE @endDate DATETIME = DATEADD(SECOND, -1, @today);

Time is usually very critical when working with dates, make sure your start date starts at the beginning of the day and your end date ends at the very end of the day!

Your query would then look like: 

SELECT *
FROM my_table
WHERE my_date_column BETWEEN @startDate AND @endDate

Upvotes: 0

Related Questions