Date Diff and Leap Year 1

[ibwebn65]

This is probably a simple question. I have been asked to get a list of accounts that have closed within five years of the account being open.

I have a table called ACCOUNTS that contains the following columns:

ACCOUNT_ID,
DATE_OPENED,
DATE_CLOSED

I'm probably on the wrong track, but I've been trying to use DATEDIFF to figure out the number of days between the account's open and closed dates. One thing I definitely do not understanding is how to account of leap years. A five year period could have either one or two leap years. Does anyone have any suggestions on what the SELECT statement would look like?

 

[Rhys666]

Why not do a DATEDIFF in years instead of days?

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra

Church of the Flying Spaghetti Monster

 

[druer]

I think you can use the DATEADD function to just add 5 years to your starting value, and then compare your ending value to it. I've pasted a sample to illustrate:

if '01/28/2005' < dateadd(yy,5, '11/30/2000')
select 'It is less than 5 years'
else
select 'It is more than 5 years'

That will return less than 5 years.

Hope this helps

 

[ibwebn65]

druer,

That looks like it will give me what I need. Thanks!!!