Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Date problem in SQL

Re: Date problem in SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: 1998/11/13
Message-ID: <364b487b.116358885@inet16.us.oracle.com>#1/1

On Fri, 13 Nov 1998 20:40:42 +0100, bea_at_algonet.se (Bjorn E. Andersson) wrote:

>I have a table xxx with columns a, b, c, d
>where column d is date, including time in HH24MI format.
>
>How can I do a select with all records between
>last Friday 0000 and sysdate?

Try:

select *
  from foo
 where d between trunc(sysdate - decode(to_char(sysdate,'D'),

         6,7,7,1,1,2,2,3,3,4,4,5,5,6 )) and sysdate /

eg.

SQL> select * from foo;

         A          B D                  C
---------- ---------- --------- ----------
                      06-NOV-98
                      05-NOV-98
                      13-NOV-98
                      13-NOV-98
                      13-NOV-98
                      12-NOV-98
                      08-NOV-98
                      12-AUG-98

8 rows selected.

SQL> l
  1 select *
  2 from foo
  3 where d between
  4 trunc( sysdate - decode( to_char(sysdate,'D'),

  5                    6, 7, 7, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6 )) and
  6* sysdate
SQL> /
         A          B D                  C
---------- ---------- --------- ----------
                      06-NOV-98
                      13-NOV-98
                      13-NOV-98
                      13-NOV-98
                      12-NOV-98
                      08-NOV-98

6 rows selected.

Assuming that if the day is Friday then we will go back to the previous Friday. If that is not true and if today if Friday and you only want those for today then change the decode to read

decode(to_char(sysdate,'D'),6,0,7,1,1,2,2,3,3,4,4,5,5,6 )

Hope this helps.

chris.

--
Christopher Beck
Oracle Corp.


>
>Actually, I want to do this in Browser 2.0 (SGI)
>
>OS Unix SGI 6.4 (Server)
> Unix SGI 6.3 (Clients)
>Oracle 7.3.3
>
>Any ideas will be appriciated.
Received on Fri Nov 13 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US