calculating total years from two dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculating total years from two dates

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-07 : 14:51:57

I've come up with this, but it does not account for leap years. I can't figure out how to get what I want. Please help.

datediff(day, @original_issued_date, getdate()) / 365

I need total years, rounded down(it seems to round properly)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 14:55:31

datediff(year,@original_issued_date, getdate())

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-07 : 15:23:12

That's what I was thinking, but reading this article made me concerned that I wouldn't get what I was looking for:

DATEDIFF Function Demystified
[url]http://www.sqlteam.com/article/datediff-function-demystified[/url]

What about this?:

select ((((datediff(day, @original_issued_date, getdate())) + ((datediff(day, @original_issued_date, getdate()) / 365) / 4))/ 365)) as years

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-07 : 15:49:15

or this?

(datediff(day, @original_issued_date, getdate())) / 365.25

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 15:53:49

I would use Month /12. It's simple and if you look at the example it seems to always correctly determine the year.

select datediff(Month,'05/01/2008','5/1/9999') / 12

select datediff(day,'05/01/2008','5/1/9999') / 365
select datediff(Week,'05/01/2008','5/1/9999') / 52

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 15:55:35

I think on the below example you want to get 7991, not 7990.833675 Correct?
select datediff(day,'05/01/2008','5/1/9999') / 365.25

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-07 : 16:47:49

What is your definition of “total years”?

A year is not a fixed length of time, so “total years” can only have a definition in terms of your application requirements.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources