Re: DECODE -- nah, use round

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:rY-dnXDOqKbCNPHdRVn-iQ_at_comcast.com...
>
> "Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
> news:nD1bc.135809$Wa.110151_at_news-server.bigpond.net.au...
> | "Sherman H." <shung_at_earthlink.net> wrote in message
> | news:kA0bc.11648$lt2.2675_at_newsread1.news.pas.earthlink.net...
> | > I have to run a query to give a column a value based on a time range.
> Can
> | I
> | > use DECODE?
> | >
> | > select decode(trans_date, trans_date>='01-Jul-2002' and
> | > trans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,
> | > from. . .
> | > where. . .
> | >
> | >
> |
> | Sherman,
> |
> | If you are using 9i and later, you can use the CASE clause
> |
> | SELECT
> | trans_date,
> | CASE
> | WHEN trans_date BETWEEN '01-JUL-2001' AND '30-JUN-2002'
> | THEN 'Fiscal2001'
> | WHEN trans_date BETWEEN '01-JUL-2002' AND '30-JUN-2003'
> | THEN 'Fiscal2002'
> | WHEN trans_date BETWEEN '01-JUL-2003' AND '30-JUN-2004'
> | THEN 'Fiscal2004'
> | ELSE NULL
> | END AS fiscal
> | FROM
> | test_dates
> | ;
> |
> | TRANS_DAT FISCAL
> | --------- ----------
> | 30-JUN-02 Fiscal2001
> | 01-JUL-02 Fiscal2002
> | 30-JUN-03 Fiscal2002
> | 01-JUL-03 Fiscal2004
> |
> | Douglas Hawthorne
> |
> |
>
> actually, i bet this could be handled with the date version of the ROUND
> function
>
> any takers? i'm on my way out, but i'll post a solution tomorrow that will
> translate any date to the proper fiscal year (unless someone else gets to
it
> first)
>
> ;-{ mcs
>
>

Mark,

You are right. It works in 9.2.0.1.

SQL> SELECT trans_date, round( trans_date , 'YEAR' ) FROM test_dates;

TRANS_DAT ROUND(TRA

--------- ---------
30-JUN-02 01-JAN-02
01-JUL-02 01-JAN-03
30-JUN-03 01-JAN-03
01-JUL-03 01-JAN-04

Douglas Hawthorne Received on Thu Apr 01 2004 - 19:28:37 CST