RE: how to trunc date column to half an hour

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 6 Mar 2007 12:11:11 -0800
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9C6B@ALVMBXW05.prod.quest.corp>

Would this fit your needs?  

decode (sign (to_number (to_char (the_date, 'MI')) - 30),

           -1, trunc (the_date, 'HH'),
           trunc (the_date, 'HH') + 1 / 48)
 

e.g.  

SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS' ; Session modifiée.  

SQL> with sample_dates as
  2 (select to_date ('00:00', 'HH24:MI') as the_date from dual

  3    union all select to_date ('00:01', 'HH24:MI') as the_date from dual
  4    union all select to_date ('00:15', 'HH24:MI') as the_date from dual
  5    union all select to_date ('00:29', 'HH24:MI') as the_date from dual
  6    union all select to_date ('00:30', 'HH24:MI') as the_date from dual
  7    union all select to_date ('00:31', 'HH24:MI') as the_date from dual
  8    union all select to_date ('00:45', 'HH24:MI') as the_date from dual
  9    union all select to_date ('00:59', 'HH24:MI') as the_date from dual
 10    union all select to_date ('01:00', 'HH24:MI') as the_date from dual
 11    union all select to_date ('01:01', 'HH24:MI') as the_date from dual

 12 )
 13 select

 14     the_date,
 15     decode (sign (to_number (to_char (the_date, 'MI')) - 30),
 16             -1, trunc (the_date, 'HH'),
 17             trunc (the_date, 'HH') + 1 / 48)
 18     as rounded_to_half_hour

 19 from sample_dates
 20 order by the_date ;  

THE_DATE ROUNDED_TO_HALF_HOU
------------------- -------------------

2007/03/01 00:00:00 2007/03/01 00:00:00
2007/03/01 00:01:00 2007/03/01 00:00:00
2007/03/01 00:15:00 2007/03/01 00:00:00
2007/03/01 00:29:00 2007/03/01 00:00:00
2007/03/01 00:30:00 2007/03/01 00:30:00
2007/03/01 00:31:00 2007/03/01 00:30:00
2007/03/01 00:45:00 2007/03/01 00:30:00
2007/03/01 00:59:00 2007/03/01 00:30:00
2007/03/01 01:00:00 2007/03/01 01:00:00
2007/03/01 01:01:00 2007/03/01 01:00:00

10 ligne(s) sélectionnée(s).  

SQL>

---

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Derya Oktay Envoyé : mardi, 6. mars 2007 10:16
À : oracle-l
Objet : how to trunc date column to half an hour

Hi All,
Is there a way of displaying date columns, truncated to half an hours.

For example: select trunc(sysdate,'HH') from dual;

Regards,
Derya.

FYI. We are usig this sort of functions in group by expressions.

PS. Ghassan thank you for your answer regarding local/global index es in partitions.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 06 2007 - 14:11:11 CST