Menu
I want to convert sql server datetime to milliseconds . I tried to convert it with datediff function as below :
select cast(Datediff(ms, '1970-01-01',GETUTCDATE()) AS bigint)
But it's giving me this error:
Msg 535, Level 16, State 0, Line 2 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
I don't wanna do it like this :
select cast(Datediff(minute, '1970-01-01',GETUTCDATE()) AS bigint)*60*1000
Because it won't give me accurate results. Can somebody please help me on this?
531
We can use DATEPART() function to get the MILLISECOND part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as millisecond or mi .
You can get the time in seconds using time. time function(as a floating point value). To convert it to milliseconds, you need to multiply it with 1000 and round it off.
Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
For the people still looking for this, you can use the DATEDIFF_BIG function. This is supported in SQL 2016+, Azure
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
