Re: Problems with Oracle functions.

From: William Robertson <williamr2019_at_googlemail.com>
Date: 8 Mar 2007 06:24:03 -0800
Message-ID: <1173363843.780087.25530@h3g2000cwc.googlegroups.com>

On Mar 7, 9:28 pm, "Ora" <ed.roh..._at_gmail.com> wrote:
> I am trying to convert a number 20070303 into a date format column I
> have tried a combination like this but I don;t seem to be able to find
> the right syntax.
>
> insert into temp_pa
> (PRIOR_AUTH_CODE_NUM, eff_date, term_date)
> select PRIOR_AUTH_CODE_NUM,
> to_date(to_char(EFFECTIVE_DATE,'99999999'),'YYYYMMDD'),
> to_date(to_char(TERMINATION_DATE,'99999999'),'YYYYMMDD')
> from PRIOR_AUTHORIZATIONS;
>
> Help!!

Works for me (10.2.0.2). What error do you get?

WITH prior_authorizations AS

    ( SELECT 19951231 AS effective_date

           , 20070303 AS termination_date
      FROM   dual )

SELECT TO_DATE(TO_CHAR(effective_date, '99999999'), 'YYYYMMDD') AS eff_date

     , TO_DATE(TO_CHAR(termination_date, '99999999'), 'YYYYMMDD') AS term_date
FROM prior_authorizations;

EFF_DATE TERM_DATE

----------------- -----------------
31-DEC-1995       03-MAR-2007

1 row selected. Received on Thu Mar 08 2007 - 08:24:03 CST