Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select query using to_date
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.
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
Received on Tue Aug 17 2004 - 05:57:07 CDT