0

I am loading the New York Trip data (https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page) from parquet files to to an Azure SQL database. The date is in the format TIMESTAMP_MICROS, what seems to be a timestamp as bigint. I could load the data to SQL Server.

Now I want to add a persisted computed column to convert the timestamp to a datetime. For the normal SELECT from the TOP 1000 the following query shows the proper format:

SELECT TOP 1000
CONVERT
    (
        datetime2,
        DATEADD(S,[tpep_dropoff_datetime] / 1000000, CONVERT(datetime,'1970-01-01',120))
    )
AS [pickup_datetime_]
FROM [dbo].[NYTaxi]

I tried to add a computed column with the following query:

ALTER TABLE [dbo].[NYTaxi]
    ADD [pickup_datetime] AS
    CONVERT
    (
        datetime2,
        DATEADD(S, CAST(CAST([tpep_pickup_datetime] AS BIGINT) / CAST( 1000000 AS INT) AS INT), CONVERT(datetime,'1970-01-01',120))
    ) 
    PERSISTED;

But I always get the following error:

Msg 8115, Level 16, State 2, Line 8
Arithmetic overflow error converting expression to data type int.

Does anyone have an idea why the error happens? How can I solve that? If a date would be in the wrong format, I would also be happy to return a NULL value instead.

Thom A
  • 88,727
  • 11
  • 45
  • 75
denzel
  • 33
  • 7
  • `DATEADD` expects an `int` not a `bigint`. If the expression for your second value overflows an `int` it will cause an error when passed to `DATEADD`. Unfortunately, though there is a `DATEDIFF_BIG` there is not `DATEADD_BIG`, so you'll need to make the value smaller first. – Thom A Jan 17 '23 at 16:05
  • Does this answer your question? [Convert unix epoch timestamp to TSQL datetime](https://stackoverflow.com/questions/14507649/convert-unix-epoch-timestamp-to-tsql-datetime) – Jeroen Mostert Jan 17 '23 at 16:15
  • 1
    I'd suggest finding out what your bad values out first though. YOu can do with something like `SELECT tpep_pickup_datetime FROM dbo.YourTable WHERE TRY_CAST(CAST([tpep_pickup_datetime] AS BIGINT) / CAST( 1000000 AS INT) AS INT) IS NULL AND tpep_pickup_datetime IS NOT NULL;` – Thom A Jan 17 '23 at 16:15
  • Perhaps these answers will help: https://stackoverflow.com/questions/15030474/sql-using-dateadd-with-bigints – Dan Guzman Jan 17 '23 at 16:23
  • @Larnu I divide the BIGINT by 1 million and CAST it to INT. So I don't see a point that is not working here – denzel Jan 17 '23 at 17:52
  • As we can't run you code, we can only guess. If you provide a [mre] we can give a more definitive reason. The fact remains that you *are* getting a value greater than an `int` can hold. You might believe you are, but the error says otherwise. – Thom A Jan 17 '23 at 17:56
  • @Larnu your approach with TRY_CAST helped! I had a few rows with a wrong date (about 60 years in the future). Like that I could figure that out. Now I check if the date is in the past, then I transform the formula, if the date is in the future I return a NULL. Thank you so much! – denzel Jan 18 '23 at 10:06

1 Answers1

0

Your code looks fine for now.

DECLARE @tpep_pickup_datetime varchar(20) = '2147483647000000'

SELECT DATEADD(S, CAST(CAST(@tpep_pickup_datetime AS BIGINT) / CAST( 1000000 AS INT) AS INT), CONVERT(datetime,'1970-01-01',120))

Returns 2038-01-19 03:14:07.000.

Any higher and you hit the "Year 2038 problem" but as tpep_dropoff_datetime is

The date and time when the meter was disengaged.

And the data set is for NYC taxis, not time machines, so you should be fine for now.

I would also be happy to return a NULL value instead.

You can use

DATEADD(SECOND, TRY_CAST(CAST(tpep_pickup_datetime AS BIGINT) / 1000000 AS INT), '19700101')

to convert these massively outlying values to NULL with no error and also use this expression to identify rows to look at in more detail (just garbage data or something else going on?).

Martin Smith
  • 438,706
  • 87
  • 741
  • 845