CODEWITHSUNDEEP
sqlsql-serverdate
Petey B
Petey B

Reputation: 11569

Retrieve SQL Records Based on Past Date?

I have some database records, and one of the columns contains the date the record was added (called COMP_DATE).

I need to make a query, that will run each day, which selects records whose COMP_DATE is exactly n years, 11 months, and 15 days ago (within a 24 hour window). (I.E. 15 days before n years ago).

What would be the best way of doing this? Should I just subtract (n*356 - 15 days)? How would I accomplish this?

Thanks.

Upvotes: 1

Views: 212

Answers (4)

Kharaone
Kharaone

Reputation: 597

the best way will be to do it the other way around to obtain n years 11 months and 15 days ago Remove n+1 years and add 15 days, you'll have the less room for errors

DECLARE @n as INT=<your value here>;

SELECT *
FROM   records
WHERE  comp_date BETWEEN Dateadd(DAY, 15, Dateadd(YEAR, -@n-1, Getdate()))
   AND Dateadd(DAY, 16, Dateadd(YEAR, -@n-1, Getdate()));

Hope that helped ;)

Upvotes: 1

Joey
Joey

Reputation: 433

if comp_date is datetime column type, you'll want to search on a range (a 24 hour range). Also, if you are looking for 15 days before n years ago, the query should look like this:

select * from records where
comp_date >= dateadd(year,-n,dateadd(day, -15, getdate()))
and comp_date < dateadd(year,-n,dateadd(day, -14, getdate()));

If indeed you are wanting n years 11 months and 15 days ago, use WOPRs answer.

Upvotes: 0

WOPR
WOPR

Reputation: 5393

select * from records where
comp_date = dateadd(year,-n,dateadd(month,-11,dateadd(days, -15, GetDate())));

*assumimg comp_date is of type Date.

Upvotes: 0

theglauber
theglauber

Reputation: 29665

There are functions to do date arythmetic. For example, Dateadd

(Assuming this is Microsoft SQL Server based on the question's tags.)

Upvotes: 0

Related Questions