Re: Different date format

From: Gérard EDMOND <vikram_at_wanadoo.fr>
Date: Fri, 4 May 2001 17:40:39 +0200
Message-ID: <9cuk0d$nm$1@reader1.fr.uu.net>

Hope the details below might brush you.

You had given the query as,

select add_months(to_date(sysdate, 'DD-MON-YYYY'), -299) from dual;

Here sysdate is already in date format and why do you worry of converting it to a specific format. "Let Oracle to do it". When U force so the year is rounded to 2 digits resulting in bad calculations. Let me explain with an example.

GED > select add_months(sysdate, -16) from dual;

        ADD_MONTH


        ---------

        04-JAN-00

2.     GED> select add_months(to_date(sysdate, 'DD-MM-YYYY'), -16) from

dual;

        ADD_MONTH


        ---------

        04-JAN-00

Fine upto here.

1.1 GED> select add_months(sysdate, -17) from dual;

        ADD_MONTH


        ---------

        04-DEC-99

2.1     GED > select add_months(to_date(sysdate, 'DD-MM-YYYY'), -17) from

dual;

        ADD_MONTH


        ---------

        04-DEC-01

Here the 2.1 is miscalucated 'ofcourse because of us' because 00-01 results in 01

and "select add_months(to_date(sysdate, 'DD-MM-YYYY'), -29) from dual;" becomes as

        ADD_MONTH


        ---------

        04-DEC-02

Here goes the problem.

If U still insist in manual manipulations then you query should be as,

GED> select (add_months(to_date(to_char(sysdate, 'DD-MM-YYYY'), 'DD-MM-YYYY'), -299))from dual;
(ADD_MONT

04-JUN-76 Its what I had found. I might be also misunderstood this one !!!!!!!!!!

You might also question that why it works when the nls_date_format is set to DD-MM-YYYY. The logic is simple as the example explains below

select add_months(to_date(sysdate, 'DD-MM-YYYY'), -16) from dual; "The same as in example 2"

        ADD_MONTHS(


        -----------

        04-JAN-2000

        Here its not 00 rather 2000.

Gérard

"Erika Grondzakova" <Erika.Grondzakova_at_cern.ch> wrote in message news:3AF2A3EF.FF2AFAE2_at_cern.ch...

> Hello,
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> With the Partitioning option
> JServer Release 8.1.7.0.0 - Production
>
> Why do I get a different output for following selects?
>
> SQL> alter session set nls_date_format='DD-MON-YY';
>
> Session altered.
>
> SQL> select add_months(to_date(sysdate, 'DD-MON-YYYY'), -299) from dual;
>
> ADD_MONTH
> ---------
> 04-JUN-24
>
> SQL> select add_months(sysdate, -299) from dual;
>
> ADD_MONTH
> ---------
> 04-JUN-76
>
> For the session date format 'DD-MON-YYYY' I get a correct output...
>
> SQL> alter session set nls_date_format = 'DD-MON-YYYY';
>
> Session altered.
>
> SQL> select add_months(to_date(sysdate, 'DD-MON-YYYY'), -299) from dual;
>
> ADD_MONTHS(
> -----------
> 04-JUN-1976
>
> SQL> select add_months(sysdate, -299) from dual;
>
> ADD_MONTHS(
> -----------
> 04-JUN-1976
>
> Thank you in advance,
>
> Erika

Received on Fri May 04 2001 - 10:40:39 CDT