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
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@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.
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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
