Sql: Using Dateadd with Bigints

SQL: Using DATEADD with bigints

According to MSDN, in DATEADD (datepart , number , date )

number is an expression that can be resolved to an int that is added
to a datepart of date. User-defined variables are valid. If you
specify a value with a decimal fraction, the fraction is truncated and
not rounded.

Also notice that even if you give number as an integer, depending on your date & datepart, it could overflow the max range of the date which is 31-12-9999 for sql server 2008

Number has to be an integer. Here is a Test Demo

Convert bigint epoch in SQL Server

If you don't have epoch values greater than 5103129600 (which will allow you you to have dates up to 2106-02-07T06:28:13) this will work:

SELECT DATEADD(SECOND,2551564800 % 2147483647, DATEADD(SECOND,2147483647 * (V.Epoch / 2147483647),'19700101'))
FROM (VALUES(CONVERT(bigint,2551564800))) V(Epoch)

Arithmetic overflow error converting expression to data type int(How to convert 18 digits integer to datetime)

I managed to get something remotely sensible using this calc, which rounds up to minutes:

DECLARE @Input BIGINT =  
132706767376983975 / 10000000

SELECT @Input = @Input / 60

select dateadd(minute, @Input, CAST('1601-01-01' AS DATETIME2(7)))

2021-07-13 19:05:00.0000000

This isn't quite the same as the correct answer though (powershell)

[datetime]::FromFileTime("132706767376983975")

Wednesday, 14 July 2021 5:05:37 AM

But I'm guessing this has to do with time zones.

How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

This worked for me:

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

In case any one wonders why 1970-01-01, This is called Epoch time.

Below is a quote from Wikipedia:

The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.

The Year 2038 Problem

Furthermore the DataAdd function takes an int for the seconds to add. So if you are trying to add more than 2147483647 seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.

Declare @t as bigint = 4147483645

Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add

-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))

This will let you convert a timestamp that represents a year greater than 2038.

Convert specific BigInt to DateTime in T-SQL

Here are some calculations that can calculate the bigint to a datetime.

SELECT
tick.value

-- Subtrack the amount of ticks for 1900-01-01 and divide that number by the ticks in 1 day.
-- Then cast or convert that smaller number to a datetime
-- But only accurate to the second.
-- 864000000000 = (10000000 * 24 * 60 * 60)
 , CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as DateTimeCalc1

-- Subtrack the amount of ticks for 1900-01-01 and divide by the ticks in 1 minute.
-- Then add that smaller number as minutes to 1900-01-01
-- Only accurate to the minute
 , DATEADD(MINUTE, ((tick.value - 599266080000000000) / 600000000), CAST('1900-01-01' AS DATETIME)) as DateTimeCalc2

-- Same method as for DateTimeCalc2, but include the milliseconds.
-- Accurate to the millisecond
 , DATEADD(MILLISECOND, FLOOR((((tick.value - 599266080000000000)/10000)%60000)), DATEADD(MINUTE, FLOOR((tick.value - 599266080000000000)/600000000), CAST('1900-01-01' AS DATETIME))) as DateTimeCalc3

FROM (values 
  (convert(bigint,635307578922100000))
 ,(convert(bigint,599266080000000000))
 ,(convert(bigint,630823257457000000))
 ,(convert(bigint,646602048000000000))
) AS tick(value);

Result:

value               DateTimeCalc1           DateTimeCalc2           DateTimeCalc3
------------------ ----------------------- ----------------------- -----------------------
635307578922100000 2014-03-18 16:44:52.207 2014-03-18 16:44:00.000 2014-03-18 16:44:52.210
599266080000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
630823257457000000 2000-01-01 12:15:45.697 2000-01-01 12:15:00.000 2000-01-01 12:15:45.700
646602048000000000 2050-01-01 00:00:00.000 2050-01-01 00:00:00.000 2050-01-01 00:00:00.000

With a bit of tampering those datetimes can be truncated or rounded on the seconds.

SELECT tick.value

-- Truncated
, CAST(CONVERT(varchar, CAST((tick.value - 599266080000000000) / 864000000000 AS datetime),20) AS datetime) as DateTimeTruncated

-- Rounded
, CAST(CAST(CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as datetime2(0)) AS datetime) as DateTimeRounded

-- For dates between 1981-12-14 and 2118-01-19, one could add seconds to 2050-01-01. 
, DATEADD(SECOND, ((tick.value - 646602048000000000) / 10000000), cast('2050-01-01' as datetime)) as DateTimeSecondsAdded

FROM (values 
(630823257457000000),
(635307578922100000),
(662380857456770000)
) tick(value);

DateAdd side effects?

Cause

Thanks to @MartinSmith for the clue.

The cause is query auto-parameterization and the data types being chosen to store values.

Query 1 is auto-parameterized:

StatementText="SELECT CONVERT([datetime],MAX([TS])+@1/@2)
....
<ColumnReference Column="@2" ParameterCompiledValue="(24)" ParameterRuntimeValue="(24)" />
<ColumnReference Column="@1" ParameterCompiledValue="(1.0)" ParameterRuntimeValue="(1.0)" />

Query 2 is not auto-parameterized:

StatementText="SELECT convert(datetime, max(TS) + 1.0/24) as A...."

Why it happens is the first query and not the second query is a bit of a black magic.

From SQL Server data types page:

When you use the +, -, *, /, or % arithmetic operators to perform
implicit or explicit conversion of int, smallint, tinyint, or bigint
constant values to the float, real, decimal or numeric data types, the
rules that SQL Server applies when it calculates the data type and
precision of the expression results differ depending on whether the
query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce
different results. When a query is not autoparameterized, the constant
value is first converted to numeric, whose precision is just large
enough to hold the value of the constant, before converting to the
specified data type. For example, the constant value 1 is converted to
numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always
converted to numeric (10, 0) before converting to the final data
type. When the / operator is involved, not only can the result type's
precision differ among similar queries, but the result value can
differ also. For example, the result value of an autoparameterized
query that includes the expression SELECT CAST (1.0 / 7 AS float)
will differ from the result value of the same query that is not
autoparameterized, because the results of the autoparameterized query
will be truncated to fit into the numeric (10, 0) data type.

Effect

Based on the above, the following data types are used (refer to See: Precision, scale, and Length (Transact-SQL) for explanation of how result types are calculated):

Query 1 gives higher precision:

NUMERIC( 2, 1 ) / NUMERIC( 10, 0 ) = NUMERIC( 13, 12 )

Query 2:

NUMERIC( 2, 1 ) / NUMERIC( 2, 0 ) = NUMERIC( 7, 6 )

Solution

Cast your literals and / or intermediate results to the desired type to avoid surprises.
In your specific case, best solution is not to use number arithmetic to manipulate dates as Panagiotis Kanavos explains in his answer.

Alternatively, forcing float data types (per Dan Guzman comment) convert(datetime, max(TS) + 1e/24) would do the trick as well.

This question deals with the same issue.

---

Related Topics