How to convert VARCHAR value for '1:00 PM' (time only) to TIME as '13:00' using-CodePudding

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'.

CodePudding user response：

Use this:

SELECT STR_TO_DATE('1:00 PM', '%h:%i %p');

Returns: 13:00:00

And the mistake was when using AM/PM format the hours lie till 12 not 24. So, when you are using %H its giving you the same hour irrespective to AM/PM.

CodePudding user response：

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'.