• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1049
  • Last Modified:

convert 12 hr am/pm time to just hhmm in mysql

Hi Experts,

I'm using Scriptcase and in the onvalidate event, i want to format what the user entered as HH:MM (am/pm) to just bring in the military time, but i don't want it to show the colon, so if it is entered as 12:15 PM i would like it to convert to 1215

How can i do this?

I have this
{FormatTime}= Time_Format({ApptTime},hhii);

Is this the correct way?
Thanks :-)

  • 2
2 Solutions
Kevin CrossChief Technology OfficerCommented:
Laura, close.  It is TIME_FORMAT({AppTime}, '%H%i').
Kevin CrossChief Technology OfficerCommented:

I tested some scenarios, just to verify my understanding was correct and this may also be of a help if you are getting in the values as a string.

STR_TO_DATE('12:15 am', '%h:%i %p')

Open in new window

This will ensure that your string is read as 00:15 millitary time.  MySQL doesn't fail is you just do this:

TIME_FORMAT('12:15 am', '%H%i')

Open in new window

It will instead run and return value "1215".  It basically will parse the time value as military time in the first place.  Therefore, if you first convert the value specifying it is in AM/PM (%p) format, then it will properly result in "0015".

So the final solution is:

TIME_FORMAT(STR_TO_DATE({AppTimeString}, '%h:%i %p'), '%H%i')

Open in new window

Hope that helps!


linbayzakAuthor Commented:
Thanks so much.  I really appreciate the help.  Both will work, but mwvisa1's solutions takes into account the am/pm factor.

have a great day :-)

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now