Q:

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.

A:

It's returning NULL because MySQL isn't successfully parsing the string into a valid DATETIME value.
To fix the problem, use the STR_TO_DATE function to parse the string into a TIME value,
SELECT STR_TO_DATE('8:48:30 AM', '%h:%i:%s %p') 

Then, to get the TIME value converted to a string in a particular format, use the 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

