Re: Select query using to_date

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
Received on Wed Aug 18 2004 - 08:45:47 CDT