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.
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!
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
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.
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.
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
