convert time string into time with AM/PM in MySQL

I have the old database in which time was stored as string with AM/PM. One day I required to do some time calculation on that field. I have googled out for convert time string into time with AM/PM in MySQL and found some solution.

Here is the Database tables:

Now by these records, I want to get the time availability of the employee by calculating time difference between inTime and outTime fields.

To do this in MySQL we use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.


So for calculating time difference in above given records, use following steps:
1. convert time string fields into date/time format
2. By using timediff function, get the difference between these 2 fields

This query will result as follows

Like this Post? Share