Search code examples
mysqltimecasting

How to convert VARCHAR value for '1:00 PM' (time only) to TIME as '13:00' using a MySQL query?

I have a VARCHAR column in table, containing time value in a format as '1:00 PM'. I need to convert it to TIME format like '13:00'.

I tried below queries but nothing worked -

SELECT STR_TO_DATE(pickup_time, '%H:%i') as pt FROM `tbl_name`;

Returned 'NULL'.

SELECT CAST(pickup_time as time) as pt FROM `tbl_name`;

Returned '01:00'.

SELECT STR_TO_DATE(CAST(pickup_time as time), '%H:%i') as pt FROM `tbl_name`;

Returned 'NULL'.

Expected output is '13:00'.

Solution

  • I have added some logic in query to get expected result as below -

    SELECT (CASE WHEN (SUBSTRING_INDEX((CASE WHEN (SUBSTRING_INDEX(pickup_time, ' ', -1)='PM') THEN CONCAT((SUBSTRING_INDEX(pickup_time, ':', 1)+12), ':',SUBSTRING_INDEX(SUBSTRING_INDEX(pickup_time, ' ', 1),':',-1)) ELSE SUBSTRING_INDEX(pickup_time, ' ',1) END),':',1)) <= 9 THEN CONCAT('0',(CASE WHEN (SUBSTRING_INDEX(pickup_time, ' ', -1)='PM') THEN CONCAT((SUBSTRING_INDEX(pickup_time, ':', 1)+12), ':',SUBSTRING_INDEX(SUBSTRING_INDEX(pickup_time, ' ', 1),':',-1)) ELSE SUBSTRING_INDEX(pickup_time, ' ',1) END)) ELSE (CASE WHEN (SUBSTRING_INDEX(pickup_time, ' ', -1) = 'PM') THEN CONCAT((SUBSTRING_INDEX(pickup_time, ':', 1)+12), ':',SUBSTRING_INDEX(SUBSTRING_INDEX(pickup_time, ' ', 1),':',-1)) ELSE SUBSTRING_INDEX(pickup_time, ' ',1) END) END) as pt from `tbl_name`;

    This returns result as '13:00' if column value is '1:00 PM'.