Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Date problem in SQL
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 )) and6* sysdate
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.Received on Fri Nov 13 1998 - 00:00:00 CST
>
>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.