SQLTeam.com | Weblogs | Forums

Converting float value to smalldatetime is giving future date

sql2012

#1

Hi,

I am trying to read a float value from an MDF file and it is always giving the future date. Meaning actual date +2
Below is my query;

Select convert(smalldatetime, max(analysis_time)) from image where patientId = 12345;

Value for analysis_time is -- 42699.0861
The above query is returning -- 2016-11-27 02:04:00 .
But when i convert the float value to date in MS Excel (format cell to date) it is giving correct date 25-Nov-2016.

Also

When i read the same float value from MDB file with below query(using access driver), it is returning the correct date

Select timestamp(max(analysis_time) * 86400000 - 2209161600000) from image where patientId = 12345

As the timestamp is not a valid function in sql server i am trying use convert function to read the MDF.

Can someone help me what would have caused for this discrepancy reading the mdf file.

Thanks,
Ben


#2

see: http://stackoverflow.com/questions/24867004/error-when-converting-float-to-datetime

Especially this:

Stop storing dates as float


#3

It is some third party software that is creating the MDF files.
And I am not seeing any error converting float to datetime. But the function i am using is showing future date.
Any help would be much appreciated!


#4

Are all dates off by 2 days or just some?


#5

All dates.


#6

At least it's consistent!

Did you try this:
First you must convert the FLOAT to a VARCHAR. And since FLOAT has a number of decimal points, it must first be converted to an INT.

DECLARE @myDate FLOAT
SET @myDate = 20140721
SELECT CAST(CAST(@myDate AS INT) AS VARCHAR(8))
--20140721
Then you can convert the VARCHAR to DATE or DATETIME format.

DECLARE @myDate FLOAT
SET @myDate = 20140721
SELECT CAST(CAST(CAST(@myDate AS INT) AS VARCHAR(8)) AS DATE)
--2014-07-21

#7

The problem is that Access uses a different starting date than SQL Server. The 0 (zero) date in Access is '1899-12-30' and the 0 (zero) date in SQL Server is '1900-01-01'.

2 days difference so any float value you have defined in Access will always be 2 days difference between what you see in Access and what you see in SQL Server.

This is why we always recommend using the actual date/datetime data types. It avoids these ugly conversion type issues across different platforms.


#8

I tried this with my float value (42699.0861) but i see 'Conversion failed when converting date and/or time from character string' in your second step.


#9

In Access, i am getting the correct date with this function [Select timestamp(max(analysis_time) * 86400000 - 2209161600000) from image]

Is there a similar way to get the correct date in SQL Server as well. May be by subtracting two days from the result. Not sure if that's the correct way though.
Please advise


#10

What are the units to the left and right of the decimal point? Days and minutes? or days and seconds/microseconds? something else?

FWIW, using your input date (42699.0861) and this query:

select dateadd(second, 0861, dateadd(day, 42699, 0))

I get:

2016-11-27 00:14:21.000


#11

Expected result is 2016-11-25 02:04:00 while i get 2016-11-27 02:04:00. With two days difference. :frowning:


#12

Why can you not just subtract two days as part of your equation?


#13

That's the only option which I am currently doing now.
And looking for any other cleaner solution.