Hi all, I need to calculate the di 1

[bperry]

Hi all,
I need to calculate the difference between two dates in a table, but I need the result accurate to a couple of decimal points (example 2.53). I'd like to do something like:

Select DATEDIFF(yy, Date1, Date2) as MyDifference

but of course DATEDIFF() returns an integer, thereby losing the decimals.

Has someone already written a fancy Select statement, or perhaps a handy UDF, that can provide the result I need?

many thanx,

bp

P.S. Solution neeeds to take leap years into account (of course).

 

[JamesLean]

As a suggestion, try starting by getting the difference in hours/minutes (depending on how accurate you want it) using DATEDIFF().

You should then be able to convert that value to a decimal and do some simple division to get the years. --James

 

[bperry]

>>and do some simple division to get the years.

Because of the issue with leap years, I'm thinking that the division is not that simple. (i.e. there isn't a fixed number of seconds in a year.) But perhaps I'm making it more difficult than it is. Does anyone have a working example available?

bp

 

[bperry]

Okay, I think I may have this solved.
I want the difference in years between two dates, accurate to two decimals. Like 23.17 years, for example.

I am breaking up the calculation into two parts: the years component (to the left of the decimal) and then the fractional component (to the right of the decimal).

The years component is just a standard DATEDIFF calculation:
DATEDIFF(yy,ServiceDate,PenDate)

The fractional component is kinda ugly, but basically I take the number of days left over (from above) and divide it by 365.

ROUND(
CAST(DATEDIFF(dd,DATEADD(yy,DATEDIFF(yy,ServiceDate,PenDate),ServiceDate),Pendate) as Decimal(8,2)) / 365.00
,2)

So for example if ServiceDate was 1965-11-12, and PenDate was 2000-12-31, I take the number of days between 2000-12-12 and 2000-12-31, and that's the fractional component.

So altogether, it looks like this:

Select DATEDIFF(yy,ServiceDate,PenDate)
+
ROUND(
CAST(DATEDIFF(dd,DATEADD(yy,DATEDIFF(yy,ServiceDate,PenDate),ServiceDate),Pendate) as Decimal(8,2)) / 365.00
,2) as YearsDifference
---------------------------------------
This seems to work on my test data. But if any Sql gurus have an easier way to get the difference in years, accurate to two decimals, with leap years considered, I'll take any suggestions.

bp

 

[SQLSister]

I would suggest you make this a user defined function in case you need it in more than one place.

 

[bperry]

A UDF is a good thought, thanks. (Note: Need SQL 2000.)
Thanks,
bp