Converting a float to datetime 2

[robertfah]

We use a 3rd party software for some tracking information and they store the date/time a item was received in a FLOAT format: 20080408144419 would be 04/08/2004 2:44:19PM

How can I convert this float to display meaningful data as in 04/08/2004 2:44:19PM ?

Any ideas?

 

[genomon]

Do a BOL search on CAST; choose the subcategory "Using CAST and Convert". Should be pretty helpful.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[robertfah]

Thanks for the link, but when I try to convert or cast the float to a datetime, it get the following:

select convert(datetime, '20080627121140') - generate this:

Server: Msg 241, Level 16, State 1, Line 18
Syntax error converting datetime from character string.

and this:

select cast('20080627121140' as datetime)

gives me the same error message...???

 

[bborissov]

That is not float, that is pure string and you should have it formatted:

select convert(datetime, '20080627 12:11:40')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[robertfah]

Ok, I can't format it like that.....let me try to explain this a little better.

There's a 3rd party application that stores a datetime as a Float. If I enter the date 04/25/2004 2:44:19 PM in the application, it stores 20040425144419 in the DB field and the data type of that field is Float. So I'm trying to take that value '20040425144419' and turn it into a readable format of 04/25/2004 2:44:19 PM.

 

[genomon]

convert(datetime, '20080627121140')" is incorrect syntax for this function.
Something like "SET @varname = CONVERT(varchar(14), 'yourfield', 126)
followed by CAST(@varname AS SMALLDATETIME) might also work.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[bborissov]

Many castings just because someone can't handle datetime fields:

[COLOR=blue]DECLARE[/color] @test [COLOR=blue]float[/color]
[COLOR=blue]SET[/color] @Test = 20080626142231


[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=#FF00FF]datetime[/color],
       [COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](@Test [COLOR=blue]as[/color] BigInt) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](14)),8)+[COLOR=red]' '[/color]+
       [COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](@Test [COLOR=blue]as[/color] BigInt) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](14)),9,2)+[COLOR=red]':'[/color]+
       [COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](@Test [COLOR=blue]as[/color] BigInt) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](14)),11,2)+[COLOR=red]':'[/color]+
       [COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color](@Test [COLOR=blue]as[/color] BigInt) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](14)),13,2)
       )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[genomon]

I agree with Boris - I was just too lazy to pound out all the code he did. Thanks, B, for showing a better way!

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."