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: 65150
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-00936: missing expression
Next Topic: Exceptions
Goto Forum:
  


Current Time: Mon Aug 21 16:45:59 CDT 2017

Total time taken to generate the page: 0.06996 seconds