Home » SQL & PL/SQL » SQL & PL/SQL » Not a valid month (oracle 10g)
Not a valid month [message #430612] Wed, 11 November 2009 03:34 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

SELECT CASE
          WHEN SYSDATE
                 BETWEEN '01-jan-'
                         || TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR')
                     AND LAST_DAY (ADD_MONTHS (:pdate, -12))
             THEN 'a'
          ELSE 'b'
       END
  FROM DUAL
Re: Not a valid month [message #430613 is a reply to message #430612] Wed, 11 November 2009 03:35 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Don't use implicit conversion.
Re: Not a valid month [message #430617 is a reply to message #430612] Wed, 11 November 2009 03:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
SELECT CASE WHEN SYSDATE BETWEEN to_date('01-jan-'|| TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR'),'dd-mon-yyyy')
                         AND LAST_DAY (ADD_MONTHS (:pdate, -12))
            THEN 'a'
            ELSE 'b'
       END
FROM   DUAL




Re: Not a valid month [message #430620 is a reply to message #430617] Wed, 11 November 2009 04:04 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or, hm, perhaps there's no "jan" month there?
SQL> select to_date('01-sij-2009', 'dd-mon-yyyy') From dual;
select to_date('01-sij-2009', 'dd-mon-yyyy') From dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01-sij-2009', 'dd-mon-yyyy', 'nls_date_language = croatian') from dual;

TO_DATE('01-SIJ-200
-------------------
01.01.2009 00:00:00

SQL>
Re: Not a valid month [message #430625 is a reply to message #430612] Wed, 11 November 2009 04:27 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
And, as January, 1st is the first day in the year, then
SELECT CASE WHEN SYSDATE BETWEEN TRUNC (ADD_MONTHS (:pdate, -12), 'yyyy')
                         AND LAST_DAY (ADD_MONTHS (:pdate, -12))
            THEN 'a'
            ELSE 'b'
       END
FROM   DUAL
will return the same result without any date/string conversions.
Re: Not a valid month [message #430626 is a reply to message #430612] Wed, 11 November 2009 04:27 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Instead of
to_date('01-jan-'|| TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR'),'dd-mon-yyyy')
you can use
trunc(ADD_MONTHS (:pdate, -12), 'YY')



Edit: Argh.. Flyboy beat me to it Smile

[Updated on: Wed, 11 November 2009 04:29]

Report message to a moderator

Re: Not a valid month [message #430653 is a reply to message #430612] Wed, 11 November 2009 08:08 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
seyed456 wrote on Wed, 11 November 2009 04:34
                 BETWEEN '01-jan-'
                         || TO_CHAR (ADD_MONTHS (:pdate, -12), 'RRRR')
                     AND LAST_DAY (ADD_MONTHS (:pdate, -12))


You really need to understand about implicit conversion as Thomas has suggested. Please read the concepts manual of Oracle.

Your query in essence is the same as "Is today's date between an earthworm and 1 year ago?"
Re: Not a valid month [message #430754 is a reply to message #430612] Thu, 12 November 2009 04:01 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
better write
select 'b' from dual
Smile
Re: Not a valid month [message #430759 is a reply to message #430754] Thu, 12 November 2009 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does your new avatar means "don't care of my posts they are just a silly jokes"?

Regards
Michel
Re: Not a valid month [message #430760 is a reply to message #430759] Thu, 12 November 2009 04:41 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Thu, 12 November 2009 10:32
Does your new avatar means "don't care of my posts they are just a silly jokes"?

Regards
Michel

Glass houses?
Re: Not a valid month [message #430767 is a reply to message #430759] Thu, 12 November 2009 05:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Michel wrote
Does your new avatar means "don't care of my posts they are just a silly jokes"?

Regards
Michel


i dont think a will come for any "inputted" date ,So
select 'b' from dual

Meanwhile my avatar means "Be happy"

sorry take it as joke and ignore it
as it gives a for this
 SQL>   SELECT CASE WHEN SYSDATE BETWEEN to_date('01-jan-'|| TO_CHAR (ADD_MONTHS (SYSDATE+365, -12), 
'RRRR'),'dd-mon-yyyy')
  2                           AND LAST_DAY (ADD_MONTHS (SYSDATE+365, -12))
  3              THEN 'a'
  4              ELSE 'b'
  5         END
  6  FROM   DUAL
  7  /

C
-
a

[Updated on: Thu, 12 November 2009 05:20]

Report message to a moderator

Re: Not a valid month [message #430773 is a reply to message #430767] Thu, 12 November 2009 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My post was a joke. Wink

Regards
Michel
Re: Not a valid month [message #430805 is a reply to message #430760] Thu, 12 November 2009 06:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

So
Glass houses? are always Jokes ? Laughing Wink

[Updated on: Thu, 12 November 2009 06:38]

Report message to a moderator

Re: Not a valid month [message #430811 is a reply to message #430805] Thu, 12 November 2009 06:45 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Sorry, what's your point?
Re: Not a valid month [message #430818 is a reply to message #430811] Thu, 12 November 2009 07:07 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Sorry pablolee,i misquoted it to your message,
it was actually for
Michel`s meessage Smile
Previous Topic: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Next Topic: Increment sequence value for set of rows
Goto Forum:
  


Current Time: Thu Sep 29 05:36:58 CDT 2016

Total time taken to generate the page: 0.11217 seconds