[oracle] concat yesterdays date with a specific time

I need to create a query that looks at yesterdays date but at a specific time. I would like the time portion to be in the 'hh24:mi:ss' format as this is how the search field is formated.

when I run this query it works:

where date_dt = to_date('2013-12-13' || '19:16:08','yyyy-mm-dd hh24:mi:ss') 

but when I try to query for yesterday it does not:

where date_dt = to_date(trunc(sysdate)-1 || '19:16:08','yyyy-mm-dd hh24:mi:ss') 

I get zero rows returned. any idea what im doing wrong here?

thank you.

This question is related to oracle timestamp

The answer is


where date_dt = to_date(to_char(sysdate-1, 'YYYY-MM-DD') || ' 19:16:08', 'YYYY-MM-DD HH24:MI:SS') 

should work.