Unix Timestamp to Datetime Conversion

• vamshi.sql

  SSCommitted

  Points: 1770

  More actions

  January 21, 2020 at 12:32 pm

  #3716997

  Hi,

  I have a table with one of the column [last seen] nvarchar datatype and data imported from .csv file. Data in the [last seen] column is in Unix Timestamp in MilliSecs format [1575944000000]

  I need to convert it from unix timestamp to datetime format.

   

  Tried changing the datatype of the column from nvarchar to datetime and getting below error:

  - Unable to modify table. Conversion failed when converting date and/or time from character string.

   

  Can someone please give an idea\solution to fix this issue. Thanks in advance.

• Neil Burton

  SSC-Insane

  Points: 23089

  More actions

  January 21, 2020 at 2:08 pm

  #3717044

  SELECT DATEADD(SECOND,1575944000000/1000,'1970-01-01 00:00:00.000')

  Does this work?

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537