Re: date functions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 18 Nov 1999 09:27:53 -0500
Message-ID: <hwo0OM51uhOz4nG8tZSErgS=sOSk@4ax.com>

A copy of this was sent to Jiri.Felcman_at_noofs.abb.no (if that email address didn't require changing) On Thu, 18 Nov 1999 11:56:40 GMT, you wrote:

>Does anybody know what is the easiest way to be able to use special
>date/time functions (getweeknumber, daysbetween, isyearleap ...) in
>ORACLE SQL? For example - I have statistics data calculated every 30
>minutes and I want to create a view where all data will be grouped by
>week number.
>
>Is there any PL/SQL package or should a C(C++) component be used?
>

just SQL and date arithemtic and some decodes is what you want. You can do virtually any date related grouping using SQL (and it'll be much faster then dropping out to C or plsql)

days between = trunc( abs(a-b) ) eg:

tkyte_at_8i> select sysdate, created, trunc( abs(sysdate-created) ) from all_users where username = user
  2 /

SYSDATE CREATED TRUNC(ABS(SYSDATE-CREATED))

-------------------- -------------------- ---------------------------
18-nov-1999 09:22:15 10-nov-1999 14:52:47                           7

getweeknumber = to_char( a, 'IW' ) eg:

tkyte_at_8i> select to_char( created, 'IW' ), to_char(created, 'YYYY' ), count(*)   2 from all_users
  3 group by to_char( created, 'IW' ), to_char(created, 'YYYY' )   4 /

TO TO_C COUNT(*)
-- ---- ----------

16 1999         11
17 1999          3
...[snip]
44 1999       1120
45 1999       1114
46 1999        447

27 rows selected.

isleapyear =

  1 select 1995+rownum,

            decode( to_char( to_date('2802'||1995+rownum,'ddmmyyyy')+1, 'DD' ),
  2                                                      '29', 'Leap Year',
  3                                                            'Not Leap Year' )

  4 from all_users
  5* where rownum < 10
tkyte_at_8i> /

1995+ROWNUM DECODE(TO_CHA
----------- -------------

       1996 Leap Year
       1997 Not Leap Year
       1998 Not Leap Year
       1999 Not Leap Year
       2000 Leap Year
       2001 Not Leap Year
       2002 Not Leap Year
       2003 Not Leap Year
       2004 Leap Year

9 rows selected.

>Thanks and with regards
>
>Jiri
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 08:27:53 CST