7

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

user2793907
  • 131
  • 2
  • 2
  • 4

3 Answers3

3

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.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
2
with data(time) as (
  select to_date('2013-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 06:45:44', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 08:12:25', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 18:59:59', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 19:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 20:15:35', 'YYYY-MM-DD HH24:MI:SS') from dual union all
  select to_date('2013-09-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS') from dual
)
select d.time,
case
  when to_number(to_char(d.time, 'HH24')) >= 19 then
    trunc(d.time) + 19/24
  when to_number(to_char(d.time, 'HH24')) >= 7 then
    trunc(d.time) + 7/24
  else
    trunc(d.time - 1) + 19/24
end as shift_date
from data d
;
user272735
  • 10,473
  • 9
  • 65
  • 96
0

Are you looking for a query like this:

SELECT   CASE
    WHEN TO_CHAR(your_date, 'hh24') > 12
    THEN TRUNC(your_date)+ 19/24
    ELSE TRUNC(your_date)+ 7/24
  END
FROM your_table;
ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41