Results 1 to 9 of 9
  1. #1
    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. #2
    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')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    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. #4
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    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. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote 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.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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. #9
    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
  •