hive datetime formats

Question

I need to convert 2019-07-12 22:30:00.000 to 7/12/2019.

I have used from_timestamp(dintervalstart,'M/d/yyyy') where dintervalstart is the col holding these values in impala and got succeeded. Whereas in hive I am getting the output if I use hardcoded dates but I'm not getting the output if I pass the direct column. Could you please suggest where I'm going wrong

For example: I'm getting output for this

select date_format('2019-07-12 22:30:00.000','M/d/yyyy')

but not for this

select dintervalstart,date_format(cast(dintervalstart as string),'M/d/yyyy') FROM audit.iwrkgrpqueuestats_daily_vw

similarly I'm getting output for this

select from_unixtime(unix_timestamp('2019-07-12 22:30:00.000'), 'M/dd/yyyy')

but not for this

select dintervalstart, from_unixtime(unix_timestamp(dintervalstart, 'yyyy-MM-dd HH:mm:ss.sss'), 'M/dd/yyyy') from audit.iwrkgrpqueuestats_daily_vw

please note dintervalstart is in string format

Answer 1

My second query that I have posted

select dintervalstart, from_unixtime(unix_timestamp(dintervalstart, 'yyyy-MM-dd HH:mm:ss.sss'), 'M/d/yyyy') from audit.iwrkgrpqueuestats_daily_vw

worked after setting the following property. No issues with the query

SET mapreduce.job.queuename=general;