CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Pratik
Pratik

Reputation: 11745

sql server Query to find exactly 1 year difference

Need to disable an user in the database (SQL Server) on exact completion of 1 year.
For this I have the following query which would executed by a job on timely basis.

update TOCustDetails
set IsActive = 0
where DATEDIFF(DAY,CreatedOn,(DATEADD (YEAR,1,CreatedOn)))  >= 365


But its failing on few points like :
1. Leap year handling is not done, One can do it by checking year, I know
2. I prefer not to have hardcoding like 365 or so
3. Also its not accurate because I need accuracy level up to second at least. +/- 1 second delay would be fine.

Can anyone please advice me on this?

Upvotes: 2

Views: 657

Answers (1)

Durgesh Chaudhary
Durgesh Chaudhary

Reputation: 1077

As per your question you are trying to check if expiry date has elapsed or not, so get expiry date and check if it has occured like below :

DATEADD(YEAR,1,CreatedOn) <= getdate()

Here we are calculating expiry date from DATEADD(YEAR,1,CreatedOn) and it will be as accurate as CreatedOn and compare it with current date getdate()

Upvotes: 1

Related Questions