Monday, March 22, 2010

Calculate Date Different in seconds for far apart dates

datediff() returns an integer. The max value for an integer is 2,147,483,647. That work out to be about 24855 days

select 2147483647 / 60 / 60 / 24
 
Result = 24855

This is fine for most cases but if you have bad data, you will get overflow error.


I have written a function just to calculate the different of 2 dates in terms of seconds. It returns the seconds in a bigint.


It first find the different in terms of days and then multiply by 24 x 60 x 60 to convert to number of seconds. Then it subtract and add the time different for the start date and the end date to get the total different in seconds for the 2 dates.


Here is the function.



create function fn_diffsecond
(
    @date1 datetime, 
    @date2 datetime
)
returns bigint
as
begin
    return         (convert(bigint, datediff(day, @date1, @date2)) * 24 * 60 * 60)
        -     (datediff(second, dateadd(day, datediff(day, 0, @date1), 0), @date1))
        +     (datediff(second, dateadd(day, datediff(day, 0, @date2), 0), @date2))
end

No comments:

Post a Comment