Forum Stats

  • 3,770,120 Users
  • 2,253,069 Discussions
  • 7,875,327 Comments

Discussions

Rounding Time to nearest 30 minutes

2»

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    That is nice one !!
    I arranged mine to simpler :-)
    ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
    
    with dates as ( -- generating sample data
    select to_date('12/28/2009 11:08:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
    select to_date('12/28/2009 11:18:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
    select to_date('12/28/2009 11:38:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
    select to_date('12/28/2009 11:48:19', 'mm/dd/yyyy hh:mi:ss') from dual)
    select dt,
    case when to_char(dt,'mi') between '15' and '44'
         then trunc(dt,'hh24') + interVal '30' minute
         else round(dt,'hh24') end as new
    from dates;
    
    DT                   NEW
    -------------------  -------------------
    2009-12-28 11:08:19  2009-12-28 11:00:00
    2009-12-28 11:18:19  2009-12-28 11:30:00
    2009-12-28 11:38:19  2009-12-28 11:30:00
    2009-12-28 11:48:19  2009-12-28 12:00:00
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Aketi Jyuuzou wrote:

    I arranged mine to simpler :-)
    Nice.

    SY.
This discussion has been closed.