Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Select query using to_date

Re: Select query using to_date

From: Ethel Aardvark <bigjobbies_at_hotmail.com>
Date: 17 Aug 2004 03:57:07 -0700
Message-ID: <1a8fec49.0408170257.7d6a5d8a@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. 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US