Re: Select query using to

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 18 Aug 2004 10:28:17 -0400
Message-ID: <ROmdnVuo3uoe-r7cRVn-oA@comcast.com>

"George" <georgina.wren_at_eu.watsonwyatt.com> wrote in message news:d45230d0.0408180545.1fcc0cc7_at_posting.google.com...
| bigjobbies_at_hotmail.com (Ethel Aardvark) wrote in message
news:<1a8fec49.0408170257.7d6a5d8a_at_posting.google.com>...
| > George,
| > There is a slight flaw in your apparent assumption that you can
| > perform a "to_number" on a date field.
| > You can't.
| > What Oracle does here is convert the date to a varhar2 then to a
| > number.
| >
| > This may seem petty, but if you run this code on a server set up with
| > different rules for implicit number to varchar2 conversions you may
| > get problems.
| > For example, what if numbers are displayed with thousands separators?
| > You may get "1,998" instead of "1998" - which would confuse the rest
| > of the code!
| >
| > Regards,
| >
| > ETA.
|
|
| Hi there,
|
| Thanks for your response. Any ideas what I might use instead of
| to_number or indeed if I need anything? There must be a way of just
| defining the Day and month and then determining the Year.
|
| Regards
| George
| >
| >
| > georgina.wren_at_eu.watsonwyatt.com (George) wrote in message
news:<d45230d0.0408120316.5ca78f1f_at_posting.google.com>...
| > > Hi,
| > >
| > > I'm trying to run a select query with a date parameter. The
| > > 'Tran_date' needs to be between two dates and I have a 'case'
| > > condition that determines these dates:
| > >
| > > and a.tran_date between
| > > Case when (extract(month from sysdate)) > 10
| > > then to_date('01/10' || to_number(sysdate, 'YYYY'), 'DD/MM/YYYY')
| > > else to_date('01/10' || to_number(sysdate, 'YYYY'-1), 'DD/MM/YYYY')
| > > End
| > > and sysdate
| > >
| > > If the sysdate month is greater that 10 then the date is '01/10' and
| > > the Year needs to be the current year. Else the date is '01/10' and
| > > the year is the previous year. I keep getting an 'Invalid number'
| > > error.
| > >
| > > Can anyone help with this?
| > >
| > > Thanks in advance
| > > George

ok, it looks like your logic is:

if we are in jan to october, get everything since october of last year otherwise (we are in november, december) get everything since october of this year
right?

well, actually, i think you would want to check for october, november and december, but you logic seems to just want to check for nov/dec -- i'll illustrate it with oct/nov/dec

then you need something like

a.trans_date between

        to_date( '01/10/'||to_char(
            case
            when extract(month from sysdate) in (10,11,12)
            then   extract(year from sysdate) -1
            else   extract(year from sysdate)
            end
            )
           ,'/dd/mm/yyyy'
        )
        and sysdate

but you may want to consider creating a reusable function

    create or replace function fiscal_year_start     return date
    is
    begin

        return
        to_date( '01/10/'||to_char(
            case
            when extract(month from sysdate) in (10,11,12)
            then   extract(year from sysdate) -1
            else   extract(year from sysdate)
            end
            )
           ,'/dd/mm/yyyy'
        );

end;

so that you can do:

a.trans_date between fiscal_year_start and sysdate

also, consider using meaningful table aliases -- a table alias like 'A' makes your SQL really hard to maintain

note: code not guaranteed to be correct, used to illustrate concepts only -- don't cut and past into your project and blame me!

++ mcs Received on Wed Aug 18 2004 - 09:28:17 CDT

Re: Select query using to_date