Convert unix timestamp to date in Microsoft SQL Server

Question

I am trying to convert the 10 digit unix timestamp value to proper yyyy-mm-dd hh:mm:ss format using the code in Microsoft SQL server

Update data
set Time_final= dateadd(S,[unix_date], '1970-01-01 00:00:00.0')
from data

But I am not getting the output in the desired format. For example:

for the value 1599313801 which we are supposed to get 2021-04-17 14:25:59, I am getting 13:50:01.0000000

That would suggest that `time_final` has a type of `time` rather than `datetime` of some sort. — Gordon Linoff, Apr 17 '21 at 14:30
`select dateadd(second, 1599313801, cast('1970-01-01 00:00:00.0' as datetime))` is `2020-09-05 13:50:01.000` . Why expect something else? — Serg, Apr 17 '21 at 14:41

score 1 · Answer 1 · answered Apr 17 '21 at 14:31

1

I might suggest that you just do this as a computed column:

alter table data add real_date as (dateadd(second, unix_date, '1970-01-01'));

The value is then computed when you query the table. You don't have to worry about the values getting out of synch.

answered Apr 17 '21 at 14:31

Gordon Linoff

2

Depending on the requirements, it might also be worth persisting this column too. (Though you'll have to add an explicit convert and a style code to do so.) – Thom A Apr 17 '21 at 14:32
As [this answer](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) shows `DATEADD` suffers from the Y2038 problem. The two-part conversion in the answer would fix that. So would a SQLCLR call to `DateTimeOffset.FromUnixTimeMilliseconds` – Panagiotis Kanavos Sep 22 '22 at 15:07

1 Answers1