none
Difference of Dates in Millisecond

    Question

  • Hi,

    In our system we are considering 01-01-1970 as base date to take the difference between the datetime and store it in Table after considering the datepart in ss. For e.g.

    Datediff(ss, '19700101',GETDATE())

    the above will give the numerical value like for eg. 4568978323.

    Now instead of considering DatePart as SS(Seconds) i want to use MS(Milliseconds) and take the difference from 19700101. However i found that the max difference when using MS is only 24 days as compared to SS which is 68 years.

    Is there any other way where i can store Datetime in Milliseconds. Please let me know about the same. Please note the base date of 01-01-1970 will not change.

    Regards,

    Saumik Vora

    Tuesday, September 06, 2011 5:10 AM

All replies

  • Hi Vora

    I think you are using datatype INT  to store the data returned by datediff function, you have to use BIGINT and if you use the BIGINT, you can store upto 684931506849315068493150684 years approximately (by using millisecond difference only).

    You have to use the explicit cast to get it as bigint, check the example below

    select cast(datediff(ms,getdate()-1,getdate()) as bigint)
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, September 06, 2011 5:30 AM
  • The problem is that DateDiff() returns an int, not a bigint.  so if the parameters you pass to it cause it to try to return a number larger than the largest possible int, (which the time in milliseconds from Jan 1, 1970 to today will do), it will error out before you get to the Cast as bigint.  So what you need to do is get the difference from the Jan 1, 1970 date to 00:00:00.000 today in some larger time unit, like minutes, convert that value to a bigint, then multiply it by the conversion factor to get milliseconds (60,000 if you used minutes), then add in the time in milliseconds from midnight to now.  So, assuming you are on SQL 2008 or later,

    Select Cast(Datediff(minute, '19700101', Cast(GETDATE() As date)) As bigint) * 60000 + Datediff(ms, '19000101', Cast(GETDATE() As time))
    

    Tom

    Tuesday, September 06, 2011 5:45 AM
  • Hi Surendra,

    The mere Statment

    SELECT DATEDIFF(MS,'19700101',GETDATE())

    gives me overflow error as the return type of DATEDIFF is INT. And as mentioned the startdate will always remain 01-01-1970

    Saumik Vora

    Tuesday, September 06, 2011 5:48 AM
  • Tom is right (mybad I should have checked with the your base date).

    the datediff function returns the value in a INT datatype and in this case the value is too large to hold in INT. 

    Approach to handle this issue:

    1. caliculate the number of seconds difference between the two dates 
    2. convert the seconds to the milliseconds (multiply by 1000)
    3. add the milliseconds of your date to the result in the step 2.

    select cast(datediff(ss,'19700101',getdate()) as bigint) * 1000 --- steps 1 & 2
    + cast (datepart(ms,getdate()) as bigint) -- step 3
    


     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, September 06, 2011 6:11 AM
  • Here's another way to do it:

    SELECT CAST(CAST(GETDATE()-'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)
    


    Tuesday, September 06, 2011 6:30 AM
  • Here's another way to do it:

     

    SELECT CAST(CAST(GETDATE()-'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)
    


     

    in this method, I think you are missing the milliseconds of the getdate().... although you are converting the resultant date into a millisecond, but the number of milliseconds in the getdate() are not included in the caliculation to the matter of fact not even the hours....

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, September 06, 2011 6:39 AM
  • Here's another way to do it:

     

    SELECT CAST(CAST(GETDATE()-'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)
    


     

    in this method, I think you are missing the milliseconds of the getdate().... although you are converting the resultant date into a millisecond, but the number of milliseconds in the getdate() are not included in the caliculation to the matter of fact not even the hours....

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Actually, it does. Converting a datetime value to decimal datatype returns the "time" part in the decimal part of the value.
    Tuesday, September 06, 2011 6:50 AM
  • Hello All,

    I tried to what Tom had suggested and worked fine. Now i have another question. How to convert the value(of the above mentioned solution) in Datetime format showing DateTime with milliseconds also.

    Regards,

    Saumik Vora

    Tuesday, September 06, 2011 6:59 AM
  • You can use the same logic to convert the value back to datetime.

    i.e. Split the value to smaller values then use dateadd function

    SELECT GETDATE()
    	,DATEADD(ms, t % 60000, DATEADD(mi, t/60000, '19700101'))		--convert ms value back to datetime
    FROM
    	(SELECT
    		Cast(Datediff(minute, '19700101', Cast(GETDATE() As date)) As bigint) * 60000 + Datediff(ms, '19000101', Cast(GETDATE() As time)) t  --Tom's solution
    	) T
    


    Tuesday, September 06, 2011 7:20 AM
  • Neither of the previous suggestions takes care of the millisecond part. See http://www.sqlteam.com/article/datediff-function-demystified how DATEDIFF works.

    Try this suggestion.
    DECLARE	@FromDate DATETIME2(3) = '19700101 00:00:00.000',
    	@ToDate DATETIME2(3) = '20110906 09:39:44:365'
    
    DECLARE	@StartDate DATE = @FromDate,
    	@EndDate DATE = DATEADD(DAY, 1, @ToDate)
    
    -- Solution by SwePeso
    SELECT	CAST(86400000 AS BIGINT) * DATEDIFF(DAY, @FromDate, @ToDate)
    	- DATEDIFF(MILLISECOND, @StartDate, @FromDate)
    	- DATEDIFF(MILLISECOND, @ToDate, @EndDate)
    


    N 56°04'39.26"
    E 12°55'05.63"
    Tuesday, September 06, 2011 7:46 AM
  • You Try Converting Seconds To Date Instead of Ms

     

    SElect Convert(Bigint,Datediff(ss, '1970-01-01',GETDATE()))*1000 -- To Milliseconds
    
    
    
    select DATEADD(ss,(1315314682000/1000),'1970-01-01')  --From MS To Date
    
    



    Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Tuesday, September 06, 2011 7:47 AM
  • Neither of the previous suggestions takes care of the millisecond part. See http://www.sqlteam.com/article/datediff-function-demystified how DATEDIFF works.

    Try this suggestion.
    DECLARE	@FromDate DATETIME2(3) = '19700101 00:00:00.000',
    	@ToDate DATETIME2(3) = '20110906 09:39:44:365'
    
    DECLARE	@StartDate DATE = @FromDate,
    	@EndDate DATE = DATEADD(DAY, 1, @ToDate)
    
    -- Solution by SwePeso
    SELECT	CAST(86400000 AS BIGINT) * DATEDIFF(DAY, @FromDate, @ToDate)
    	- DATEDIFF(MILLISECOND, @StartDate, @FromDate)
    	- DATEDIFF(MILLISECOND, @ToDate, @EndDate)
    


    N 56°04'39.26"
    E 12°55'05.63"

    It will probably return incorrect results when the start month is greater than the end month according to your study, but that will never happen since the start date is already fixed to '1970-01-01'.

    Also, my original suggestion doesn't even use Datediff for the computation, though I should have used ROUND than +0.5 to round off the value.

    Btw, your query returns a negative value when @ToDate = @StartDate.

    Tuesday, September 06, 2011 8:14 AM
  • Saumik,

    Why do you need such a huge time range in milliseconds?

    The following article deals with age calculation:

    http://www.sqlusa.com/bestpractices2005/employment/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Monday, September 12, 2011 7:56 AM
    Moderator