FLOAT to DATETIME

Question

FLOAT to DATETIME

jarceri

SSC-Addicted

Points: 439
More actions
June 13, 2008 at 2:37 pm

#192289

I have a datamodel that I am doing some reporting on and the datetime stamps are stored in the table as a float.
Example: 20080613123001
Trying to convert this to a datetime seems like an easy task but it overflows everything I have tried to convert or cast to.
Any help would be great.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

jarceri (6/13/2008)
I have a datamodel that I am doing some reporting on and the datetime stamps are stored in the table as a float.
Example: 20080613123001
Trying to convert this to a datetime seems like an easy task but it overflows everything I have tried to convert or cast to.
Any help would be great.

Do all values meet the following format: YYYYMMDDhhmnss?

😎

jarceri SSC-Addicted Points: 439 More actions · Answer 2

jarceri

SSC-Addicted

Points: 439

June 13, 2008 at 2:49 pm

#828816

Yes they are all that way yyyymmddhhmmss

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

it's unfortunately got no idea what to do with the float you threw at it. You're likely going to have to cast it back to a varchar, and do some manipulations on it.

On one it would look something like:

declare @dte varchar(25)

set @dte=20080613123001;

select dateadd(second,substring(@dte,13,2)*1,dateadd(minute,substring(@dte,11,2)*1, dateadd(hour,substring(@dte,9,2)*1, cast(left(@dte,8) as datetime))))

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jarceri SSC-Addicted Points: 439 More actions · Answer 4

I was thinking that but did not want to take any performance hit by converting it to a varchar. This seems like the only way to do this. Your code works by the way and thank very much for the assistance.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is some code to start working with:

declare @ADatetime float,

@DatePart int,

@TimePart int

set @ADatetime = 20080613123001

set @DatePart = round(cast(@ADatetime/1000000 as decimal(18,6)),0,1)

set @TimePart = (cast(@ADatetime/1000000 as decimal(18,6)) - @DatePart) * 1000000

select

@ADatetime,

cast(@ADatetime/1000000 as decimal(18,6)),

@DatePart,

@TimePart,

cast(cast(@DatePart as char(8)) + ' ' +

stuff(

stuff(cast(@TimePart as char(6)), 5, 0, ':')

,3,0,':') as datetime)

😎

jarceri SSC-Addicted Points: 439 More actions · Answer 6

Ok this works however it is still a float in the table I am querying therfore can't be converted to a varchar easily, at least not for me anyway.

jarceri SSC-Addicted Points: 439 More actions · Answer 7

WOW! Remind me to never use the float datatype for storing datetime data :). This is greatness let me try it out.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

jarceri (6/13/2008)
WOW! Remind me to never use the float datatype for storing datetime data :). This is greatness let me try it out.

Okay. Never use a float type to store datetime data, use datetime.

:w00t:

😎

jarceri SSC-Addicted Points: 439 More actions · Answer 9

LOL. I am not responsible for the design of this datamodel. I just inherited the headache of it.

Thanks again for your help.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

It might be a little bit slower than a whole bunch of math, but it sure is simple thanks to STR...

DECLARE @FloatDateTime FLOAT

SET @FloatDateTime = 20080613023001

SELECT CAST(STUFF(STUFF(STUFF(STR(@FLOATDATETIME,14),13,0,':'),11,0,':'),9,0,' ')AS DATETIME)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)