Post reply

Precise DATEDIFF()

  • December 1, 2005 at 10:23 am

    #88819

    Hello guys.

    I need to get the time the employee has been working, but I get that a DATEDIFF is innacurate in terms of the month-day strict compliment.

    By example, this sentence returns 2, while strictly should return 1 because Dec-15 hasn't still ocurred.

    PRINT DATEDIFF(Year, '2003-12-15', '2005-12-01')

    Do you know any workaround to this?

    Thanks

  • December 1, 2005 at 10:41 am

    #607132

    have a look at DateDiff2 on this site


    * Noel

  • December 1, 2005 at 10:48 am

    #607138

    Great!!

    That function is shorter than the one I was building

    Thanks!

  • December 1, 2005 at 4:46 pm

    #607234

    SELECT DATEDIFF(Year, 0, @Date2 - @Date1)

    _____________
    Code for TallyGenerator

  • December 1, 2005 at 5:00 pm

    #607237

    Good one, however:

    SELECT DATEDIFF(Year, 0, convert(datetime,'2005-12-14')-convert(datetime,'2003-12-15'))

    Will return 2, a "-1" completes it

    SELECT DATEDIFF(Year, 0, convert(datetime,'2005-12-14')-convert(datetime,'2003-12-15')-1)

    Thanks!

  • December 2, 2005 at 2:14 am

    #607272

    Your problem seems similar to the problem how to calculate someones age. You want to count years, but within days precision depending on if the date of the birthday has passed or not. For this, you can't use year as datepart, you must use days to reach that granularity. The reason is that datediff does only count 'boundries crossed' for the selected unit. ie for year, if the diff is between 2 years (2003-2005) then two year-bondries are crossed, thus the answer is 2. Also, there is a 'design desicion' to make if the current day is the 'birthday' or 'boundry-day' - should you count it or not?

    I usually use the below when I need to find out stuff like this:

    -- 2003-03-11 / Kenneth Wilhelmsson

    -- Determining current age notes.

    -- To be able to determine current age based on DOB date, you first must

    -- decide which way to round if the birthday is today.

    -- If the new year should be counted, round up (ceiling),

    -- if the new year should be counted tomorrow, round down (floor)

    -- current age rounded down

    declare @dob char(8)

    set @dob = '19620311'

    select floor(datediff(day, @dob, getdate()) / 365.25)

    go

    -- rounded up

    declare @dob char(8)

    set @dob = '19620311'

    select ceiling(datediff(day, @dob, getdate()) / 365.25)

    go

    /Kenneth

Viewing 6 posts - 1 through 5 (of 5 total)

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