# Thread: DateDiff - Return Months in Decimal?

1. Registered User
Join Date
Dec 2003
Posts
22

## Unanswered: DateDiff - Return Months in Decimal?

I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?

Thanks,
Rob

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
I guess you're looking for the decimal because you want what percentage of the month?

Do you need to know the number of days in that month?

It's not really a straight forward kind of thing.

I mean is this really 2 months?

SELECT DATEDIFF(m,'6/30/2004','8/31/2004')

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')

3. Registered User
Join Date
Aug 2003
Location
Kingston, Ontario
Posts
106
A value of 1.5 days has meaning since it always comes out to 36 hours or x minutes or whatever. A value of 1.5 months has no meaning since months are not all the same length. 1.5 Februarys has less days than 1.5 Marchs.

Or this one being 1?

SELECT DATEDIFF(m,'6/30/2004','7/1/2004')
Seems like it if I'm at work...

4. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
You could do years with a decimal...

check out the bio

http://www.sqlteam.com/forums/pop_pr...isplay&id=7198

5. Registered User
Join Date
Aug 2003
Location
Kingston, Ontario
Posts
106
.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover...

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by grahamt
.695890

Cool... I'll use that formula next time I have a government form to fill out. Drive them nuts for a change.

Only problem is that years don't have 365 days in them. Actually around 365.25 I think. Hence the 'makeup' day in leap years. Since time is a human concept, why not just make every month 30 days. Year end would see us 4 days short but Dec 31st we could make 120 hours long vs. 24. That would keep the calendar in sync with the seasons. Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover...

That's funny.....

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Actually, the solar year is pretty close to 365.2425 terrestrial days. So we just... Oh nevermind, I've been down this road WAY too many times now!

-PatP

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by grahamt
Unfortunately though, Jan 1st, at only 24 hours would not have enough time to recover from the 120 hour hangover...
You can recover from a hangover ?!?! We just figured it would never end, so we never allow the hangover to start!

-PatP

9. Registered User
Join Date
Aug 2003
Location
Kingston, Ontario
Posts
106
A pre-req for most DBA positions for sure. Listening to end users gripe for 8 hours a day used to be painful. Since I took up drinking I can now blissfully tune them out. Boss came to me years ago and said he felt my drinking was interferring with my work. I replied that my work was interferring with my drinking and quit on the spot. Never looked back. Next round's on me!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•