-2

I have a table that contains data imported in the epoch date format (exported from Influx , now stored as varchar(50)). I need to convert this to human readable datetime.

I have tried

 SELECT [DATE],DATEADD(S, CONVERT(BIGINT, SUBSTRING([DATE], 4,10)), '19700101')
FROM [DIM_GO_GOROUTINES]

But that returns

enter image description here

Whereas I know the date should be Monday 15th Feb !

Simon Shadowshow
  • 25
  • 4
  • Please learn to use a Search Engine. This has literally been asked [1000's](https://www.google.com/search?q=SQL+Server+-+Convert+Epoch+to+datetime+site%3Astackoverflow.com) of times: [SQL Server - Convert Epoch to datetime](https://www.google.com/search?channel=fs&client=SQL+Server+-+Convert+Epoch+to+datetime) – Thom A Feb 24 '21 at 14:44

2 Answers2

1

Well, I think this substring is what causes problem for you. When I write it only like this:

select DATEADD(SS, CONVERT(BIGINT, '1613347200'), '19700101')

I get result: 2021-02-15 00:00:00.000

Yana
  • 785
  • 8
  • 23
0
select  dateadd(s, [Date], '19700101') as mydate from [DIM_GO_GOROUTINES]
Allen King
  • 2,372
  • 4
  • 34
  • 52
  • Code dumps do not make for good answers. You should explain *how* and *why* this solves their problem. I recommend reading, "[How do I write a good answer?"](//stackoverflow.com/help/how-to-answer). This can help future users learn and eventually apply that knowledge to their own code. You are also likely to have positive feedback/upvotes from users, when the code is explained. – John Conde Feb 24 '21 at 20:49
  • It is just a simple SQL, very self-explanatory, especially in light of the text on this page and `DateAdd` is not invented by me. It can't be looked upon easily. – Allen King Feb 24 '21 at 21:25