sql - Why does MySQL DATE_FORMAT function return NULL when formatting a TIME value? -

select  date_format('8:48:30 am', '%h:%i:%s')

it returns null why ?

but when using

select  date_format(curtime(), '%h:%i:%s')

it return formatted value.

it's returning null because mysql isn't parsing string valid datetime value.

to fix problem, use str_to_date function parse string time value,

select str_to_date('8:48:30 am', '%h:%i:%s %p')

then, time value converted string in particular format, use time_format function, e.g. 24-hour clock representation:

select time_format(str_to_date( '8:48:30 am', '%h:%i:%s %p'),'%h:%i:%s')

returns:

-------- 08:48:30

EXIT