0

I have column of timestamps named a_timestamp in table named dataset4_do_edycji - formatted as epoch timestamp. I need to update it do human readable in this format: dd.mm.yyyy hh.mm.ss.ms. I tried using

UPDATE dataset4_do_edycji SET  a_timestamp=(select dateadd(ms, a_timestamp%(3600*24*1000), 
dateadd(day, a_timestamp/(3600*24*1000), '1970-01-01 00:00:00.0')))

However it throws error:

Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query.

At the moment a_timestamp is declared as bigint. When I run the following it shows correct values:

select dateadd(ms, a_timestamp%(3600*24*1000), 
    dateadd(day, a_timestamp/(3600*24*1000), '1970-01-01 00:00:00.0')
) as a_timestamp
FROM dataset4_do_edycji

Is there any way to update it except downloading both files in CSV and combining them manually to one and later importing it as new table? Thank you.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Aleksander Kuś
  • 47
  • 8
  • 2
    Date and time datatypes don't have a format; at best convert the epoch to a date and time value and then worry about the format in the presentation layer. As for how to do that, there are plenty of examples how to on [so], such as [converting Epoch timestamp to sql server(human readable format)](https://stackoverflow.com/q/4787827/2029983), [Convert Epoch to DateTime SQL Server](https://stackoverflow.com/q/32267688/2029983) and [Convert unix epoch timestamp to TSQL datetime](https://stackoverflow.com/q/14507649/2029983). – Thom A Dec 04 '20 at 20:49
  • Thank you very much, i'll check that out :) – Aleksander Kuś Dec 04 '20 at 21:03

1 Answers1

0

One simple method is to add a computed column:

alter table dataset4_do_edycji add a_timestamp_dt as
    (dateadd(ms, a_timestamp%(3600*24*1000), 
             dateadd(day, a_timestamp/(3600*24*1000), '1970-01-01 00:00:00.0')
            )
    )

The column value is then calculated when you query it, so it is always up-to-date.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786