Re: Select query using to_date

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<ROmdnVuo3uoe-r7cRVn-oA_at_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

Hi,

Thank you, with a few adjustments that worked perfectly! Well done on understanding my logic too!!

Thanks again
George Received on Thu Aug 19 2004 - 04:00:43 CDT