CODEWITHSUNDEEP
sqlsql-serverdatetimewhere-clause
CAR
CAR

Reputation: 41

Get last 90 days records (SQL)

I have the following query to get the last 90 days records from my DB.

Sample Data below:

sample data

my query code:

SELECT
Email
,Country
,Date_of_Birth
,Date_Added
,Received_ProfileCompletionPromoCode
,First_Name
,Purchase_since_entry
,Exit_Date
FROM
Profile_Completion_Journey_Exit_Log
WHERE
Exit_Date >= DATEADD(d, -90, GETDATE())

But I am getting the result where Exit_Date is 10/11/2020. What would be my error here?

Upvotes: 2

Views: 19619

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If you want data from 90 days ago, then use:

WHERE Exit_Date >= DATEADD(day, -90, CONVERT(DATE, GETDATE())) AND
      Exit_Date < DATEADD(day, -89, CONVERT(DATE, GETDATE()))

This gets results from exactly 90 days ago.

Note the conversion to DATE. Despite its name, GETDATE() has a time component.

Upvotes: 0

GMB
GMB

Reputation: 222402

Your code gives you records whose date is not older than 90 days ago.

If you want records whose date is exactly 90 days ago, then:

WHERE Exit_Date = DATEADD(day, -90, CONVERT(DATE, GETDATE())

The conversion to date is an important step. GETDATE() returns the current date and time: we need to truncate the time part.

This assumes that Exit_Date is of date datatype. If it has a time component, then:

WHERE Exit_Date >= DATEADD(day, -90, CONVERT(DATE, GETDATE())
  AND Exit_Date <  DATEADD(day, -89, CONVERT(DATE, GETDATE())

Upvotes: 2

Related Questions