RE: how to trunc date column to half an hour

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 6 Mar 2007 16:44:29 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3602EE8545@CWYMSX04.Corp.Acxiom.net>

Mark Bobak posted an example a while back for just this situation:

VARIABLE nearest_min NUMBER;
EXEC :nearest_min := 30
SELECT sysdate

     , TRUNC(sysdate,'HH') + 
       (TRUNC((sysdate - TRUNC(sysdate,'HH')) * 24 / (:nearest_min/60)) + 1) / (60 / :nearest_min) / 24

  FROM dual;

SYSDATE TRUNC(SYSDATE,'HH'

------------------ ------------------

04-MAR-07 13:58:46 04-MAR-07 14:00:00 Dave
 

---

David C. Herring, DBA  |   A c x i o m  Delivery Center Organization 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jason Heinrich Sent: Tuesday, March 06, 2007 1:45 PM
To: deryaoktay_at_gmail.com; oracle-l
Subject: Re: how to trunc date column to half an hour

It's been taking up to an hour for me to get list messages, so apologies if someone has already given you this answer.

SELECT CASE WHEN to_number(to_char(sysdate,'MI')) < 30 THEN trunc(sysdate,'HH') ELSE trunc(sysdate,'HH') + 30/(60*24) END as half_hour from dual;

You could also create a function with the same logic and use it like the trunc command.

On 3/6/07 12:16 PM, Derya Oktay wrote:
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.

---

Jason Heinrich
Oracle Database Administrator
Pensacola Christian College
(850) 478-8496 x2509
jheinrich_at_pcci.edu

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 06 2007 - 16:44:29 CST