RE: how to trunc date column to half an hour

• From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
• To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
• Date: Tue, 6 Mar 2007 16:44:29 -0600
```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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jason Heinrich
Sent: Tuesday, March 06, 2007 1:45 PM
To: deryaoktay@xxxxxxxxx; 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

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
Pensacola Christian College
(850) 478-8496 x2509
jheinrich@xxxxxxxx
*************************************************************************
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.