Convert Float To Datetime

I have a FLOAT field for a date which looks like this: +-----------+ | FloatDate | +-----------+ | 1012013 | -- Jan 1 | 3262013 | -- Mar 26 | 11072013 | -- Nov 7 | 10012013 |

Solution 1:

Assuming you have other values like:

3092013-- March 9th10052013-- October 5th1112013-- January 11th11012013-- November 1st

Then you can do this:

DECLARE@xTABLE(FloatDate FLOAT);

INSERT@xVALUES
( 3092013),-- -- March 9th
(10052013),-- -- October 5th
( 1112013),-- -- January 11th
(11012013);-- -- November 1st

;WITH s(d) AS (SELECTRIGHT('0'+CONVERT(VARCHAR(32), 
  CONVERT(INT, FloatDate)), 8) FROM@x),
d(d) AS (SELECTCONVERT(DATETIME, 
  RIGHT(d,4) +LEFT(d,2) +SUBSTRING(d,3,2)) FROM s)
SELECT d FROM d;

Results:

d-----------------------2013-03-09 00:00:00.0002013-10-05 00:00:00.0002013-01-11 00:00:00.0002013-11-01 00:00:00.000

In your example it would be:

;WITH s(d) AS (SELECTRIGHT('0'+CONVERT(VARCHAR(32), 
  CONVERT(INT, FloatDate)), 8) FROM dbo.OLDTABLE),
d(d) AS (SELECTCONVERT(DATETIME, 
  RIGHT(d,4) +LEFT(d,2) +SUBSTRING(d,3,2)) FROM s)
INSERT dbo.NEWTABLE
SELECT d FROM d;

Of course, you might have some bad data in there, even once you get the syntax right for valid values. Since you used FLOAT you don't get any inherent validation, so that column could contain -999 or 45671213 etc.

Do you find this painful? GOOD! It should be painful. You should go back to whoever decided to store dates in a FLOAT column and kick them in the shin. Ask them if they store salaries using a geography data type. I'm glad you are fixing this but those people should not be allowed anywhere near database design.

Finally, please don't do this:

CAST(anything AS CHAR)

Always specify a length. Here's why:

• Bad habits to kick : declaring VARCHAR without (length)

Solution 2:

if you have an eight digit number (float) you can just do this

selectcast(cast(cast([invoice date] asint) asvarchar(8)) as datetime)

Solution 3:

print (convert(float, CAST('1900-01-01 03:30'AS DATETIME)))--days 0,145833333333333
print (convert(float, CAST('1900-01-01 03:30'AS DATETIME))*24)--hours 3,5
print (convert(float, CAST('1900-01-01 03:30'AS DATETIME))*24*60)--minutes 210
print (convert(float, CAST('1900-01-01 03:30'AS DATETIME))*24*60*60)--seconds 12600

this second method is more clean

print (CAST(CAST('1900-01-01 03:30'AS DATETIME) ASFLOAT))--days 0,145833333333333print (CAST(CAST('1900-01-01 03:30'AS DATETIME) ASFLOAT)*24)--hours 3,5print (CAST(CAST('1900-01-01 03:30'AS DATETIME) ASFLOAT)*24*60)--minutes 210print (CAST(CAST('1900-01-01 03:30'AS DATETIME) ASFLOAT)*24*60*60)--seconds 12600

Solution 4:

To be able to convert Float dates into datetime format, you need to convert the float data into Varchar. This is the reason behind why @yogesh-sharma answer works.