Conversion from Milliseconds to 24hrs or 1 Day or 1:30 2

[Jacque]

Hi everyone,
CRv9
IBM DB2

I need to take the value from a field which can be, in some cases a varchar datatype (long story - losing battle) and convert it from milliseconds to one of the following formats based upon the report that it's being used in.

formats
24hrs
1 day
1:30 (1 hour 30 minutes)

Thanks in advance for your help!
Jacque

 

[MJRBIM]

Please post some samples of your varchar "milliseconds" data.

 

[Jacque]

Hi MJRBIM,

30300000 (8 hours 25 minutes)
3600000 (1 hour)
9000000 (2 hours 30 minutes)

Jacque

 

[MJRBIM]

It's two formulas as listed below...

Name : {@ConvertedHours}
Formula : Int ((ToNumber ({Table.MillisecondString})/10000)/360)


Name : {@ConvertedMinutes}
Formula : (((ToNumber ({Table.MillisecondString})/10000)/360) - (Int ((ToNumber ({Table.MillisecondString})/10000)/360))) *60

It will return your records like this...

Record MillisecondString ConvertedHours ConvertedMinutes
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - -- - - - - - - - - - - - - - -
A 30300000 8.00 25.00
B 3600000 1.00 0.00
C 9000000 2.00 30.00
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Hope it helps...

 

[Jacque]

That does help, thanks MJRBIM,
But...I guess I should have clarified the varchar datatype (long story - losing battle) part. The field was developed to be multi-use - it stores text strings such as true, false, on, off, 30300000. So I first must test to see if the contents are strings or not and I'm not sure how to do that, any suggestions?

I appreciate your help.
Jacque

 

[Reebo99]

If NumericText({Table.MillisecondString}) = true then .....


This will test if the field is numeric.

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[Jacque]

You guys are the best, thanks!
I'm giving both of you stars!
Jacque

[santa2]