1

I feel like I've read a ton of these corresponding posts such as,

converting Epoch timestamp to sql server(human readable format)

& How do I convert a SQL server timestamp to an epoch timestamp?

But can't seem to get my particular use-case working. I need to convert an epoch timestamp to a normal date/time value. Currently, the column is a nvarchar(max) type. Here's an example of one of the dates:

1478563200000

I'm trying to get it to look like the following:

2019-01-14 00:00:00.0000000

I've tried the following to no success all with the same error message:

select DATEADD(SS, CONVERT(BIGINT, baddate), '19700101') as gooddate
from table

"Arithmatic overflow error converting expression to data type int"

I've tried minutes, seconds, days, all same error message and at this point I'm about to tell the guys to send the data in a different format.

DataDog
  • 475
  • 1
  • 9
  • 23
  • Your problem is the `Convert(BIGINT....)` where you need and `int`. Convert to `bigint`, then divide by 1000 (trading in ms resolution for s resolution) and then convert to `int`. Why is the column of type `nvarchar(max)` in the first place? Why isn't it a numeric type (...like `int`)? – RobIII Feb 26 '19 at 00:15
  • What are the values of `baddate` in the `table`? Can you show example? Maybe put on a rextester.com wall? – Alex Yu Feb 26 '19 at 00:16
  • @DavidG I never said BIGINT is no numeric type, I said why the **column** is of type nvarchar (which isn't a numeric type). – RobIII Feb 26 '19 at 00:21
  • @DavidG Yes it did; though I changed the first two sentences after I realized why the bigint was used instead of int (the timestamp is in milliseconds). The part about the type still stands and is unchanged. – RobIII Feb 26 '19 at 00:22
  • Possible duplicate of [Error Converting Epoch to DateTime in SQL Server when epoch is more than a Trillion seconds](https://stackoverflow.com/questions/16728211/error-converting-epoch-to-datetime-in-sql-server-when-epoch-is-more-than-a-trill) – DavidG Feb 26 '19 at 00:30
  • Possible duplicate of [Epoch conversion to DateTime in SQL](https://stackoverflow.com/questions/53767909/epoch-conversion-to-datetime-in-sql) – David Dubois Feb 26 '19 at 03:42

2 Answers2

1

Try

select DATEADD(SS, CONVERT(INT, CONVERT(BIGINT, baddate)/1000), '19700101') as gooddate
from table

DATEADD expects an int, not a bigint. Since your timestamp is in milliseconds, it won't "fit" in an int. If you trade-in millisecond resolution for second resolution by dividing by 1000 it will fit in an integer and make DATEADD happy. So first we convert the NVARCHAR to BIGINT (why store as NVARCHAR in the first place?), then divide by 1000 and then convert to INT.

Another option is to divide the value by 1000 at the time of insert (and, again, make the column an int in the first place). That'll save a lot of CONVERTs everywhere (you can get rid of them all) and probably speed up your queries quite nicely. Then again, you could even convert the column to datetime (or datetime2 or whatever type is best suited) and leave out the entire dateadd/convert mess in your queries alltogether. Always try to get your data ad close to the final datatype you need it in later.

Edit: I just realized you can probably leave one convert out:

select DATEADD(SS, CONVERT(BIGINT, baddate)/1000, '19700101') as gooddate
from table

This is the same as the original suggestion, only this time the cast to int is implicit. But converting your data upon insert is still probably the better idea. So the rest of my post still stands.

RobIII
  • 8,488
  • 2
  • 43
  • 93
  • Dang, just needed to switch the bigint out. I do agree that the column being in NVARCHAR is kind of a pain to deal with but ultimately I believe it has to do with how the process in upserting the records. It's coming from an outside vendor so it's a bit out of my scope. – DataDog Feb 26 '19 at 01:07
  • Note, this works only for years in range 1902 through 2038. – David Dubois Feb 27 '19 at 12:07
0

You can get the correct result to the millisecond, that works with years 0001 through 9999, using the accepted answer

here.

declare @x nvarchar(max) = N'1478563200000'

select dbo.UnixTimeToDateTime2(@x)
David Dubois
  • 3,842
  • 3
  • 18
  • 36