| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: function/procedure with dates
Lvandenborre_at_puratos.be wrote:
>
> Hello,
>
> I need a procedure/function/view where I get the result of a Query in which I
> have to work with the reference towards the current date.
>
> The server is a Oracle 7.3
>
> Underneath you find the Query as I wrote it to work in Delphi but I want it to
> run on the server and get the date from server.
>
> input for the procedure
> :ID is the Id of the country in tbl_country
>
> input which now needs to come from server (calculated)
> :day : today ( example : 19 if today is january 19)
> :dayofweek : number of day of the week ( monday=2) can be changed
>
> Output
> All clients who need to be processed that day
>
> SCRIPT:
> select distinct tbl_clients.id, tbl_Clients.Clientname, tbl_clients.clientok
> from tbl_clients, tbl_country
> WHERE
> (countryid=:id) and
> (((tbl_clients.daymon = 1) AND (:dayofweek = 2)) OR
> ((tbl_clients.daytue = 1) AND (:dayofweek = 3)) OR
> ((tbl_clients.daywed = 1) AND (:dayofweek = 4)) OR
> ((tbl_clients.daythu = 1) AND (:dayofweek = 5)) OR
> ((tbl_clients.dayfri = 1) AND (:dayofweek = 6))) AND
> ((tbl_clients.week1 = 0 AND tbl_clients.week2 = 0 AND
> tbl_clients.week3 = 0 AND tbl_clients.week4 = 0 ) OR
> ((tbl_clients.week1 = 1 AND :day <= 6) OR
> (tbl_clients.week2 = 1 AND :day >= 7 AND
> :day <= 13) OR
> (tbl_clients.week3 = 1 AND :day >= 14 AND
> :day <= 20) OR
> (tbl_clients.week4 = 1 AND :day >= 21))
> ) AND
> (tbl_clients.priceclient = 0)
> and (tbl_country.id=tbl_clients.countryid)
>
> Please send answers to Lvandenborre_at_puratos.be
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Hopefully this helps....
In PL/SQL
x date := sysdate; (current date and time) x date := trunc(sysdate) (current date)
x-1 = yesterday
x-2 = day before
to_char(x,'DD') = day of month
etc etc
Check the doco for the various format masks you can use to get the appropriate stuff from dates...
--
 |
 |