Convert Days to Years/Months 1

[cglinn]

I have a query that returns a list of 2 dates, like so:

Date1 Date2
11/01/1999 04/14/2004
01/01/2003 09/23/2004
.......... ...........

What I need to do is get an average time elapsed between the two dates for the entire recordset.

So far, I loop through each row and get the difference (in days) between the two dates using the datediff() function. I add the difference to a running total to get a total number of days and then divide by the number of records to get an average number of days.

My problem is that I need to turn the average number of days into Years, Months, Days.

Any Suggestions?

 

[PC888]

I may be wrong, but it is unusual to express an elapsed time in years-month-days, unless one uses only one unit, such as 8.5 months, or 1.4 years.

Unfortunately the TimeSpan Class in VB provides Days as the largest unit possible for elapsed time.

To express an elapsed time in months, one will have to decide how many days there are in the hypothetical month (28,29,30,31,30.4167, 30.5 ...)

What I did in one of my projects was to express the elapsed time in a single unit, depending on the value, varying from seconds to minutes when it exceeds 5 minutes, then to hours, days, months and then years.

If I misunderstood your problem, let me know.

 

[chiph]

Try this:

Dim MyElapsed As DateTime
MyElapsed = DateTime.MinValue
MyElapsed.AddDays(834)  ' your days
MyElapsed = MyElapsed - DateTime.MinValue

It creates a new DateTime at the earliest date value that .NET knows about (00:00:00.0000000, January 1, 0001), then adds your number of days, then subtracts the min value again. The result should be the number of years, months & days.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first