Convert epoch to human readable date

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  March 6, 2019 at 12:20 pm

  #374076

  Hi,
  I'm trying to convert a LogTime column to date format and I keep getting the error: 

  Arithmetic overflow error converting expression to data type datetime.

  I have read a ton of articles on different ways to convert this and I keep getting the same error no matter what I try.  Here is the table structure:
  LogTime (decimal(19,0), NOT NULL)
  Message (nvarchar(40000), NULL)

  LogTime               Message
  1551279347054   Channel Test_train currently not assigned.  Error in update status to 0
  1551279348071   Channel Test_train is unassigned.  Attempting to start.
  1551279348071   Channel 'Test_train' is already assigned.
  1551279348071   already previously acquired
  1551279348087   Channel Test_train currently not assigned.  Error in update status to 0

  This table has 100M rows and all I'm trying to do is write a SQL to display rows older than 30 days.  I've tried CONVERT, CAST, DATEDIFF and I can't get it to work.  Any suggestions would be greatly appreciated.  I don't need to write a complicated stored procedure, just a simple SQL query so I can purge the data based on a date range. 🙂

  Thanks!
  Bea Isabelle

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  March 6, 2019 at 12:26 pm

  #2023709

  Divide it by 1000, to add seconds, so it seems based on the value returned:

  SELECT DATEADD(SECOND, (1551279347054/1000), '19700101')

  If you need to keep the milliseconds, you can add them afterward:

  SELECT DATEADD(MS, (1551279347054%1000), DATEADD(SECOND, (1551279347054/1000), '19700101'))

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  March 6, 2019 at 1:07 pm

  #2023721

  Hi Scott,

  Thank you!  That worked perfectly 🙂

  Thanks!
  Bea Isabelle