CODEWITHSUNDEEP
sqlt-sqldatetimetype-conversionsql-server-2014
pollux
pollux

Reputation: 181

TSQL Datetime conversion looses a period of time

i'm playing around with a MsSQL Server for some days now and stumbled upon a strange behaviour when converting a datetime to/from a decimal.

SELECT [date] = GETDATE()

, [as decimal] = CAST(GETDATE() AS decimal)
, [from decimal] = CAST(CAST(GETDATE() AS decimal) AS datetime)

, [as float] = CAST(GETDATE() AS float)
, [from float] = CAST(CAST(GETDATE() AS float) AS datetime)

--                                  should be: 2009-08-15
, [from stored float] = CAST(CAST('40039.9583333333' AS float) AS datetime)

When i convert a datetime without adding anything specific the decimal will be handled as i would define it decimal(18, 0). So there is a data loss in some way.

If i directly convert a float back to datetime which was converted from a datetime (as shown in line 7 of my query) everything is fine.

But when i load a value from a database table, for example 40039.9583333333 which definitely was calculated from a user input (2009-08-15) and convert it back to a datetime, it'll add one day.

I wasn't able to find anything specific for this time loss.

Is somebody able to describe the problem behind this strange behaviour? And, if possible: add an example on how to do those conversions correctly?

Thanks.

Upvotes: 1

Views: 55

Answers (3)

Rob
Rob

Reputation: 191

I used the values you specified '2009-08-15' and converted it to both decimal and float, which resulted in a value of 40038 for both. I used this value to convert back to datetime and both the decimal and float returns '2009-08-15 00:00:00.000'.

The value 40039.9583333333 results in, as Renan just posted :), '2009-08-16 22:59:59.997'.

I would question whether it was 'definitely was calculated from a user input (2009-08-15)' because that does not appear to be the case. There is more to it than shared.

-- Microsoft SQL Server 2014 - 12.0.4100.1 (X64)

DECLARE @dt datetime = '2009-08-15'

SELECT CAST(@dt AS decimal) -- 40038
SELECT CAST(@dt AS float) -- 40038

DECLARE @dec1 decimal = 40038;
SELECT CAST(@dec1 AS datetime) -- 2009-08-15 00:00:00.000
DECLARE @flo1 float = 40038;
SELECT CAST(@flo1 AS datetime) -- 2009-08-15 00:00:00.000

DECLARE @dec2 decimal = 40039.9583333333;
SELECT CAST(@dec2 AS datetime) -- 2009-08-17 00:00:00.000
DECLARE @flo2 float = 40039.9583333333;
SELECT CAST(@flo2 AS datetime) -- 2009-08-16 22:59:59.997

Upvotes: 1

Renan Vasconcelos
Renan Vasconcelos

Reputation: 176

In SQL Server 40039.9583333333 is the result of SELECT CAST(CAST('2009-08-16 23:00' AS DATETIME) AS FLOAT)

You should check the inputs. Check which scale/code is being used to calculate the value that is stored on your table.

Upvotes: 0

Mike R
Mike R

Reputation: 701

What was your input source into the DB? The underlying issue could be the starting reference point for the date.

As an example: From reading online, in Excel the date value '1900-01-01' is equal to 1 numerically.

However, in SQL Server the date value '1900-01-01' is equal to 0 numerically.

SELECT CAST(CAST('1900-01-01' AS DATETIME) AS FLOAT)

Upvotes: 0

Related Questions