Oracle PLSQL truncate datetime to specific hours

Question

I have an Oracle PLSQL code generating a list of datetime stamps and I would like to truncate them to the specific hours of 7am and 7pm rather than the beginning of the day.

For example:

• 01/03/2013 0700 becomes 01/03/2013 0700
• 01/03/2013 1235 becomes 01/03/2013 0700
• 01/03/2013 1932 becomes 01/03/2013 1900
• 02/03/2013 0612 becomes 01/03/2013 1900

My code is currently:

 SELECT  TRUNC(TRUNC(SYSDATE,'hh') + 1/24 - (ROWNUM) / 24, 'dd')  as shift_date
 FROM widsys.times
 ORDER BY SYSDATE

Thanks

Answer 1

Without conditionals :)

Select your_date, 
  trunc(your_date - 7/24) +                       --the date
  trunc(to_char(your_date - 7/24,'hh24')/12)/2 +  --wich half of day
  7/24                                            --shift the hour
from 
your_table;

See a fiddle.