38

I want to convert UTC milliseconds to DateTime in SQL server.

This can easily be done in C# by following code:

DateTime startDate = new DateTime(1970, 1, 1).AddMilliseconds(1348203320000);

I need to do this in SQL server. I found some script here, but this was taking initial ticks from 1900-01-01.

I have used the DATEADD function as below, but this was giving an arithmetic overflow exception by supping milliseconds as difference:

SELECT DATEADD(MILLISECOND,1348203320000,'1970-1-1')

How can I do the conversion properly?

honk
  • 9,137
  • 11
  • 75
  • 83
Mohan
  • 1,051
  • 1
  • 9
  • 22

7 Answers7

70
DECLARE @UTC BIGINT
SET @UTC = 1348203320997 

SELECT DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101'))
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • fails for value 3429993600000 with "Arithmetic overflow error converting expression to data type int.". Checked on SQL Server 2019 latest CU. – Oleksandr Mar 17 '21 at 13:12
  • @Oleksandr It is because the parameter to dateadd is an integer. It would be so much easier if dateadd could take a bigint as parameter. Vote for it here: https://feedback.azure.com/forums/908035-sql-server/suggestions/37801321-dateadd-big – Mikael Eriksson Mar 18 '21 at 12:24
  • 3
    @Oleksandr `select dateadd(millisecond, @UTC % 86400000, dateadd(day, @UTC / 86400000, '19700101'));` – Mikael Eriksson Mar 18 '21 at 12:30
5

Below the function that converts milliseconds to datetime

IF object_id('dbo.toDbTimeMSC', 'FN') IS NOT NULL DROP FUNCTION dbo.toDbTimeMSC
GO
CREATE FUNCTION [dbo].[toDbTimeMSC] (@unixTimeMSC BIGINT) RETURNS DATETIME
BEGIN
    RETURN DATEADD(MILLISECOND, @unixTimeMSC % 1000, DATEADD(SECOND, @unixTimeMSC / 1000, '19700101'))
END
GO

-- select dbo.toDbTimeMSC(1348203320000)

GLeb
  • 95
  • 1
  • 5
3

I had problems with using answers given here (especially that the system was counting ticks form 0001-01-01) - so I did this:

CONVERT(DATETIME,[Time]/ 10000.0/1000/86400-693595)

--explanation for [Time_in_Ticks]/ 10000.0/1000/86400-693595
--Time is in "ticks"
--10000 = number of ticks in Milisecond
--1000  = number of milisecons in second
--86400 = number of seconds in a day (24hours*60minutes*60second)
--693595= number of days between 0001-01-01 and 1900-01-01 (which is base
--          date when converting from int to datetime)
Tariq2k
  • 41
  • 2
  • All the other answers on this page fail due to the number being too big. The number is [638106300000000000] which is 29th Jan 2023. SELECT CONVERT(DATETIME,638106300000000000/ 10000.0/1000/86400-693595) works perfectly. THANK YOU!!! – aSystemOverload Feb 23 '22 at 11:50
3

Using SQL Server 2008R2 this produced the required result:

CAST(SWITCHOFFSET(CAST(dateadd(s, convert(bigint, [t_stamp]) / 1000, convert(datetime, '1-1-1970 00:00:00')) AS DATETIMEOFFSET), DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS DATETIME)
stinkyjak
  • 304
  • 2
  • 8
2

The DATEADD requires an integer as a second argument. Your number 1348203320000 is very large for integer therefore it produce an error in runtime. Your should use bigint type instead and provide DATEADD with correct int values by splitting your milliseconds to seconds and milliseconds. That is sample you could use.

DECLARE @total bigint = 1348203320000;

DECLARE @seconds int = @total / 1000
DECLARE @milliseconds int = @total % 1000;

DECLARE @result datetime = '1970-1-1';
SET @result = DATEADD(SECOND, @seconds,@result);
SET @result = DATEADD(MILLISECOND, @milliseconds,@result);
SELECT @result
tsionyx
  • 1,629
  • 1
  • 17
  • 34
0

Right now, you can use dateadd with division on minutes and not seconds.

The code will be like this:

DATEADD(MILLISECOND, epoch% 60000, DATEADD(MINUTE, epoch/ 60000, '19700101'));

Ygalbel
  • 5,214
  • 1
  • 24
  • 32
0
=dateadd("d",INT((Fields!lastLogon.Value / 864000000000)- 134774),"1970-01-01 00:00:00")

That's what I used in SSRS to get around the INT error, use days instead of seconds. Is it wrong?

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
  • If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/30879184) – trenton-ftw Jan 26 '22 at 06:30