Update epoch timestamp to dd.mm.yyyy hh.mm.ss.ms

Question

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.

Answer 1

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.