March 6, 2019 at 12:20 pm
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
March 6, 2019 at 12:26 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 6, 2019 at 1:07 pm
Hi Scott,
Thank you! That worked perfectly 🙂
Thanks!
Bea Isabelle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply