Convert 8-digit numeric string to Time: eg 11233077 = 11:23:30 AM

[ColinBC]

Hi all,

I have a report that I would like to convert an 8-digit numeric string (hour, minute, second, millisecond) to a proper time format. I would ideally like this to read as a 6-digit time string. I have a couple examples of how it reads in the report as is and what I would ideally like to see instead. If I have to stick to 24-hour format, that is fine, but I'd like to get the : inserted and the milliseconds excluded.

11233077 = 11:23:30 AM
16471355 = 4:47:13 PM

Any help is greatly appreciated!

Colin

 

[CoSpringsGuy]

based ONLY on the TWO examples you gave this formula will work

stringvar a := totext({yourfield},"#");
timevar b := time(tonumber(left(a,2)),tonumber(mid(a,3,2)),tonumber(mid(a,5,2)))

right click the field after placing in report and select format field and the time tab if you want to change from 24-format...

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[ColinBC]

Thanks for the quick reply! I've added the formula, however I am getting an error when I run the report:

"Crystal Reports error code 0x80047496 in Report::ReadRecords: Hour must be between 0 and 23"

I suspect this is likely because of some of the times list as 7 digits which I neglected to mention (my bad). Below are a few more examples of the current time string. I suspect it is the 7-digit times before 10am (examples on left) that may be the cause of the error:

8523992 = 8:52:39.92 AM
9072685 = 9:07:26.85 AM
9111183 = 9:11:11.83 AM
9384419 = 9:38:44.19 AM

Once again, any help is greatly appreciated! If it makes any difference, I am using CR10.

 

[CoSpringsGuy]

numbervar alen := len(totext({yourfield},"#"));
stringvar a := replicatestring("0",8-alen) & totext({yourfield},"#");
timevar b := time(tonumber(left(a,2)),tonumber(mid(a,3,2)),tonumber(mid(a,5,2)))

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Alternatively, change the first line of the original formula to:

stringvar a := totext({yourfield},"0#######");

This has the effect of padding out 7 digit numbers to 8 with a leading zero.

Cheers
Pete

 

[ColinBC]

Thanks very much, CoSpringsGuy! That did the trick!

 

[CoSpringsGuy]

sweet Pete! I like that better...

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[ColinBC]

Thanks Pete! I like having options