CR 8.5 How to convert AM/PM time to Military Time? 1

[gal]

Crystal Reports 8.5: Please provide a formula that converts a Time field containing AM/PM format to Military Time. When I compare two time fields directly, they sort as though they were strings. But, "12:01 AM" should not sort higher than "10:00 PM". Thank you.

 

[lupins46]

If the sort is ascending then 12:01 AM SHOULD sort before 10:00 PM

 

[gal]

Thank you for your response. The Sort is Ascending. I need to convert the time to Military format.

 

[Ridyen]

This converts the time to a text string but as Military time. Will this work for you?

totext({?TimeInput},"HH:mm:ss")

 

[lupins46]

You use Format Field on the right click menu fo the field.
Choose 'customize'and you can choose 24 hour format.

 

[catbert]

Do you mean converting to 24 clock? So 10 pm shows as 22:00 hours? You can set the format of any time field to 24 clock.

 

[gal]

This formula works. Thanks for all your efforts.

if Left (totext({history.time}), 2) ="12" and
Right (totext({history.time}), 2) ="AM"
then
"00" + ":" + Mid(totext{history.time}),4,2)
else
if Right (totext({history.time}), 2) ="PM"
then
ToText (CDbl (Left(totext({history.time}),2)) + 12) + ":" +
Mid ( totext({history.time}), 4, 2 )
else
Left (totext({history.time}), 2) + ":" + Mid ( totext({history.time}), 4, 2 )

 

[lupins46]

Some people just like coding -)

 

[synapsevampire]

Whole lotta work there for what you have...
now
Create a formula of time({history.time}) (assuming that you have a datetime field)

Riight click the field and select format field->Time-Customize->24 hour and adjust the hour/minutes/seconds as you prefer.

You're creating a string, not a time, so it isn't very useful and it's less efficient.

-k

 

[Ridyen]

Well I think I understood why setting the format would not work because I believe it only does that at print time. If a comparison was needed that conversion would take place too late. However my little formula gives the same result as Gals solution so my only question is does Crystal 8.5 not allow the syntax I proposed?

 

[synapsevampire]

Uuuuuuhhhhh, a time field sorts correctly, no idea what the issue is here...

If you convert it to a text string, and it has the AM/PM, then of course it will not.

Your formula seems OK, Ridyen, it just wouldn't work for time comparisons.

-k