Convert varchar string data to time format in mysql

I need to convert varchar string data to time format in mysql.

For example,

I have a varchar column in table which stores time. The accepted values should be like 9:30 AM or 1200 PM. But currently it has either blank values or it has values like 9.30am or 12:00

There are many records like this, so cannot update manually.

Ithere any work around or function or procedure to do so? please help.

Thanks

Answers


You can use the STR_TO_DATE() MySQL function to convert any string to a date.

Additionally you can use TIME() to extract the time portion of a datetime. A combination of both function is used to convert an arbitrary date string to a datetime and then you can extract the time portion from it as a valid MySQL TIME.

By default MySQL functions follow standard format but custom format can be specified and if your values don't use the international formats you'll need to check with the documentation and provide the format your system is using.


Need Your Help

Ionic infinite scroll doesn't work on all android devices

javascript cordova ionic-framework hybrid-mobile-app

I'm currently developing an ionic app and successfully implemented an infinite scroll feature. It works fine on desktop browsers and newer android devices, however, i'm having problems with phones ...

Test if quicklisp has already been installed in clisp

common-lisp clisp quicklisp

I'm working on a project in Common Lisp which makes use of a package installed with quickload. I'm making a bash script in the root of the project which tests if the necessary programs are install...