[Last Call] Learn how to a build a cloud-first strategyRegister Now


calculate difference in milli seconds

Posted on 2004-11-03
Medium Priority
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
  • 4
LVL 26

Expert Comment

ID: 12491061
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

Hilaire earned 150 total points
ID: 12491080
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

ID: 12491094
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)
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.


Author Comment

ID: 12491521
this is as good as

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

LVL 10

Expert Comment

ID: 12492201
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

ID: 12492250
>>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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 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