Post reply

DateDiff that returns real instead of int?

  • September 23, 2010 at 9:00 am

    #98906

    Hi all,

    I trying to calculate the number of years between a date (anniversay dates) and today. According to msdn DateDiff

    "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate."

    i.e. Using dates 12/31/2009 to 1/1/2010

    - 'year' returns 1

    - 'month' returns 1

    - 'day' needs to be divided by 365 or 365.25 and neither is accurate.

    - Even 'dy' divided by 365.0 isn't exact... '9/25/2000' to '9/23/2010' displays 10. Rather than 9. something.

    I am trying to calculate the number of years between anniversay dates. i.e. 9/23/2000 to 9/23/2010 = 10 regardless of leap days/years and 9/24/2000 to 9/23/2010 is less that 10 etc...

    Has anyone else come across this?

    Thank you in advance.

  • September 23, 2010 at 9:11 am

    #1226403

    This function returns age in years, and handles the specific cases you mentioned.

    Age Function F_AGE_IN_YEARS:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

  • September 23, 2010 at 9:20 am

    #1226408

    Try getting the DateDiff in hours instead then multipy it by the correct conversion factor; 24 for days, 365 or 365.25 for years, etc.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 29, 2010 at 7:59 am

    #1229036

    Michael, that function worked great.

    Thank you,

    I'm new to functions so I need to learn them. But this one did the trick.

    Thanks again.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply