How to cast sql datetime2 to double?

user_3521652 0
Viewed 3541

I have a table in SQL Server 2016 with a column named "Start" of type datetime2(0). In my application that pulls data from that table, I need to convert that columnar data to a double (I'm using a third party app that requires the data to be a double array). I've tried to convert the data to a string in this way to pass it to my application:

SELECT Cast(Start AS CHAR(19)) FROM MyTable

and then converting that string in my application to a double. This approach does not work because I cannot convert the string to a double. What is a good approach to this?

data-conversion sql-server
2 Answers

By double I assume you mean floating point.

datetime2(0) can be cast to datetime first (without loss of precision), which casts to float easily
Assuming you don't have values before 1753

SELECT CAST(CAST(Start AS datetime) AS float) FROM MyTable

Example

DECLARE @foo datetime2(0) = GETDATE()
SELECT @foo, CAST(CAST(@foo AS datetime) AS float)

-- 2018-09-20 11:31:32   43361.4802314815
GO

DECLARE @foo datetime2(0) = GETDATE()
SELECT @foo, CAST(@foo AS float) --error

--Msg 529, Level 16, State 2, Line 5
--Explicit conversion from data type datetime2 to float is not allowed.
user_275350

If by chance you do need to work with dates before 1753, you can use DateDiff_Big to count the seconds from a fixed point in time, and divide by the number of seconds in a day.

DateDiff_Big is only available since SQL Server 2016. If you have an earlier version, you can do something similar with DateDiff, but it's slightly more complicated.

declare @x datetime2(0)

set @x = '2018-09-20T22:44:07'

select @x, 
       cast ( cast ( @x as datetime ) as float ),
       datediff_big(second,'0001-01-01T00:00:00.000', @x ) / cast(86400 as float) - 693595,
       (   datediff ( day, '0001-01-01T00:00:00.000', @x ) + datediff ( second, '00:00:00.000', cast(@x as time) ) / cast(86400 as float) ) - 693595

set @x = '1804-09-20T08:35:00'

select @x, 
       cast ( cast ( @x as datetime ) as float ),
       datediff_big(second,'0001-01-01T00:00:00.000', @x ) / cast(86400 as float) - 693595,
       (   datediff ( day, '0001-01-01T00:00:00.000', @x ) + datediff ( second, '00:00:00.000', cast(@x as time) ) / cast(86400 as float) ) - 693595

set @x = '1066-09-20T08:35:00'

select @x, 
--       cast ( cast ( @x as datetime ) as float ), -- won't work, before 1753.
       datediff_big(second,'0001-01-01T00:00:00.000', @x ) / cast(86400 as float) - 693595,
       (   datediff ( day, '0001-01-01T00:00:00.000', @x ) + datediff ( second, '00:00:00.000', cast(@x as time) ) / cast(86400 as float) ) - 693595
user_10086460
Related