Change 00:00am to 00:00 AM

[djbloiss]

I have data in a field that often someone typed in 00:00am instead of 00:00 AM or 00:00pm instead of 00:00 PM.  How do I separate it so it always recognized as time?

[sergio0592]

Hi,

Try with : Timestamp(Timestamp#(Your_field, 'hh:mmTT'),'hh:mm TT')

[swuehl]

You can parse different formats using the Alt() function:

LOAD Time(Alt(Time#(Time,'hh:mmTT'),Time#(Time,'hh:mm TT'),Time)) as Time,

          Time as TimeOrig

INLINE [

Time

12:02am

12:03AM

12:04 pm

12:01 PM

];

[djbloiss]

wouldn't I have to type out every time then?

[swuehl]

Are you talking about the INLINE LOAD?

No, that's just for demonstration of the Alt() function, so you can copy & paste to your QVW and reload.

In your real app, your data source will not be an INLINE table, but a DB or file table using a FROM ... instead of the INLINE ...

[djbloiss]

This worked great except sometimes the date is in the correct format of 00:00 AM, 00:00 PM, or even 00:00 with no AM or PM.  That is removing them?  What do I do to keep those in the field as well?

[djbloiss]

awesome thank you