Re: TO_DATE() Function

From: Lee <lstrode_at_TAMPABAY.RR.COM>
Date: Mon, 20 Nov 2000 23:30:50 GMT
Message-ID: <KuiS5.49217$vc3.9776353_at_typhoon.tampabay.rr.com>

Mike,
Another option is to do a little coding based on the month returned:

create table newtable
as
select data1 newdataname1,

           data2 to_date(<date field or value>,'YYYY')*10 +
                    decode( sign(to_date(<date field or value>,'MM')-4), -1,
1,
                        decode( sign(to_date(<date field or
value>,'MM')-7), -1, 2,
                        decode( sign(to_date(<date field or

value>,'MM')-10), -1, 3, 4))) as newdataname2 from ...
...
;

The newdateaname2 field will actually be a number versus a date, but will still be sortable, as long as the 4 digit year comes first.

Hope that is helpful.
Lee

"Cathy Racicot" <racicot_at_sympatico.ca> wrote in message news:3A170BCE.389F8168_at_sympatico.ca...
> Mike,
>
> The to_date() function transalates a character input, in your case
> 01/01/1900 to a date that oracle can understand. In order for oracle to
> know what you are passing, you have to preformat the data being passed.
> In your example, the correct usage of to_date would be
> to_date('01/01/1900', 'dd/mm/yyyy'). The to_date function doesn't do the
> conversion you are looking for. You have to preprocess the data to
> extract the quarter based on the month and pass that to the to_date
> function.
>
> Hope that helps.
>
> Mike wrote:
>
> > I am trying to write a TO-DATE( ) I am creating a table using a select
> > statement :
> >
> > create table newtable
> > as
> > select data1 newdataname1,
> > data2 TO_DATE('01/01/1900', 'YYYYQ') newdataname2
> >
> > What is the correct syntax for this TO_DATE() function so that
> > conversion is done for the year and quarter (YYYYQ) ?
> >
> > Thanks
>
>
Received on Tue Nov 21 2000 - 00:30:50 CET