Converting a bigint timestamp/TIMESTAMP_MICROS to datetime2 in Azure SQL Server doesn't work - error Arithmetic overflow converting expression to int

Question

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.

Answer 1

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?).