calculate difference in milli seconds

Posted on 2004-11-03
Last Modified: 2008-01-09
calculating date difference using datediff in milliseconds is limited (For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds.).
the constraint is its return type INT.

Is there any other way to find out the diff in millisencods(no manual calculation plz..)

thanks in adv
Question by:mcprakash4u
    LVL 26

    Expert Comment

    declare @d1 datetime, @d2 datetime
    set @d1 = getdate()
    set @d2 = dateadd(hour, 5, getdate()+25) -- 25 days and 5 hours later

    select cast(datediff(day, @d1, @d2) as bigint) * 86400000 +
     datediff(millisecond, dateadd(day, datediff(day, @d1, @d2), @d1), @d2) as datediff_ms
    LVL 26

    Accepted Solution

    You can make it a user-defined function as follows

    create function ufn_datediff_ms(@d1 datetime, @d2 datetime)
    returns bigint as
        return cast(datediff(day, @d1, @d2) as bigint) * 86400000 +
     datediff(millisecond, dateadd(day, datediff(day, @d1, @d2), @d1), @d2)

    -- how to use it (don't omit the dbo/owner prefix)
    select dbo.ufn_datediff_ms('2004.01.01', '2004.01.26') as diff_in_ms

    LVL 26

    Expert Comment

    The idea is to
    - use bigint as return datatype
    - calculate the difference in days and multiply by nb of millisecs in a day (1)
    - calculate the diff in ms on the hours alone for the same day (reduce day offset) (2)
    - add (1) and (2)

    Author Comment

    this is as good as

    return datediff(ss, @d1,@d2)*1000

    LVL 10

    Expert Comment

    Bear in mind that the Datetime field in SQL only holds time to "accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). " (from BOL)


    LVL 26

    Expert Comment

    >>this is as good as
    return datediff(ss, @d1,@d2)*1000
    Not exactly the same though
    Yours rounds the results at the second

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now