Home » SQL & PL/SQL » SQL & PL/SQL » Invalid month error...
icon9.gif  Invalid month error... [message #186983] Thu, 10 August 2006 07:41 Go to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Dear All ,

I am fetching invalid month error in following query so please help me.



select NVL(to_date('22/12/2004','dd/mm/yyyy'),
                                   to_char((to_date('22/12/2005','dd/mm/yyyy') +
                                           decode(mod(to_char(to_date('22/12/2005','dd/mm/yyyy'),
                                                               'yyyy'),
                                                       4),
                                                   0,
                                                   366,
                                                   365)),
                                           'dd/mm/yyyy')) from dual 
Re: Invalid month error... [message #186984 is a reply to message #186983] Thu, 10 August 2006 07:48 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
It would help if you were not using conflicting data types. Curious, why are you trying to figure out if it is a leap year? Why don't you just use ADD_MONTHS(

SELECT NVL (TO_CHAR (TO_DATE ('22/12/2004', 'dd/mm/yyyy'), 'dd/mm/yyyy')
           ,TO_CHAR ((  TO_DATE ('22/12/2005', 'dd/mm/yyyy')
                      + DECODE (MOD (TO_CHAR (TO_DATE ('22/12/2005'
                                                      ,'dd/mm/yyyy'
                                                      )
                                             ,'yyyy'
                                             )
                                    ,4
                                    )
                               ,0, 366
                               ,365
                               )
                     )
                    ,'dd/mm/yyyy'
                    )
           )
  FROM DUAL



For example (with ADD_MONTHS), NULL case:

SELECT NVL
          (NULL --TO_CHAR (TO_DATE ('22/12/2004', 'dd/mm/yyyy'), 'dd/mm/yyyy')
          ,TO_CHAR (ADD_MONTHS (TO_DATE ('22/12/2005', 'dd/mm/yyyy'), 12)
                   ,'dd/mm/yyyy'
                   )
          )
  FROM DUAL


non-NULL case:

SELECT NVL
          (TO_CHAR (TO_DATE ('22/12/2004', 'dd/mm/yyyy'), 'dd/mm/yyyy')
          ,TO_CHAR (ADD_MONTHS (TO_DATE ('22/12/2005', 'dd/mm/yyyy'), 12)
                   ,'dd/mm/yyyy'
                   )
          )
  FROM DUAL
Re: Invalid month error... [message #186987 is a reply to message #186983] Thu, 10 August 2006 07:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Every time I see a date question round here, I am filled with this deep sense of foreboding, and this little gem does nothing to help the problem.

A working version of your query is:
select NVL(to_date('22/12/2004','dd/mm/yyyy'),
       (to_date('22/12/2005','dd/mm/yyyy') +
              decode(mod(to_char(to_date('22/12/2005','dd/mm/yyyy'),'yyyy'),4),
                    0,366,
                    365))) from dual ;


The problem with your original was the classic 'Dates as strings' problem.

You had:
SELECT nvl(DATE,CHAR) from dual;

and Oracle was attempting to convert the CHAR into a DATE using your default date format.
Sadly, the CHAR was in the format of dd/mm/yyyy and your default date format wasn't.
My fix simply removes the TO_CHAR call round the second term in the NVL.

Now, what in the name of all that's muculent and tentacled is this piece of code meant to do?????

All the code in the second term of the NVL looks like you're trying to add one year onto a date. Is there a reason you can't use
select add_months(sysdate,12) from dual;

or
select sysdate + numtoyminterval(1,'YEAR') from dual;
Previous Topic: Parsing strings from variable amount of elements
Next Topic: INSERT INTO table (xxx) SELECT a,b,c FROM view doesn't work in 10g but works in 8i
Goto Forum:
  


Current Time: Sun Dec 04 02:25:05 CST 2016

Total time taken to generate the page: 0.04009 seconds