Home » SQL & PL/SQL » SQL & PL/SQL » Help with truncating minutes
Help with truncating minutes [message #227035] Tue, 27 March 2007 03:22 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi there,

does anybody know the best way to truncate minutes into quarters?

example


10.3.2007 0:02 => 10.3.2007 0:00
10.3.2007 0:14 => 10.3.2007 0:00
10.3.2007 0:20 => 10.3.2007 0:15
10.3.2007 14:59 => 10.3.2007 14:45


thanks !
Re: Help with truncating minutes [message #227039 is a reply to message #227035] Tue, 27 March 2007 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select to_date('10.3.2007 0:02','DD.MM.YYYY HH24:MI') t from dual
  3    union all
  4    select to_date('10.3.2007 0:14','DD.MM.YYYY HH24:MI') from dual
  5    union all
  6    select to_date('10.3.2007 0:20','DD.MM.YYYY HH24:MI') from dual
  7    union all
  8    select to_date('10.3.2007 14:59','DD.MM.YYYY HH24:MI') from dual
  9    )
 10  select to_char(trunc(t) +
 11                 trunc(to_number(to_char(t,'SSSSS'))/(15*60))*(15*60)/86400,
 12                 'DD.MM.YYYY HH24:MI')
 13  from data
 14  /
TO_CHAR(TRUNC(T)
----------------
10.03.2007 00:00
10.03.2007 00:00
10.03.2007 00:15
10.03.2007 14:45

4 rows selected.

Regards
Michel
Re: Help with truncating minutes [message #227043 is a reply to message #227039] Tue, 27 March 2007 03:38 Go to previous message
dusoo
Messages: 41
Registered: March 2007
Member
Hii,

that's perfect,


thanks !
Previous Topic: ORA-01007: variable not in select list
Next Topic: How do I expand the number of characters to be displayed on one line?
Goto Forum:
  


Current Time: Sun Dec 11 04:14:49 CST 2016

Total time taken to generate the page: 0.08739 seconds