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

Posted on 2011-05-06
Last Modified: 2012-06-21
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 :-)

Question by:linbayzak
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Laura, close.  It is TIME_FORMAT({AppTime}, '%H%i').
    LVL 59

    Accepted Solution


    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!



    Author Closing Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now