1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01843: not a valid month

Discussion in 'SQL PL/SQL' started by StryderKC, Jan 14, 2010.

  1. StryderKC

    StryderKC Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hi I am writing a larger query but giving a smaller example. Basically I have a weakness with the date formats, so i always mess up. This is a minor example of what I have:
    Code (SQL):

    SELECT   LAST_UPDATE_DATE
      FROM   po_headers_all
     WHERE   LAST_UPDATE_DATE >= '01-10-2005 00:00:00'
    It gives me ORA-01843: not a valid month, where did I mess up?

    Thanks...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,409
    Likes Received:
    350
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    By not using TO_DATE() and a proper date format mask foir the supplied string:

    Code (SQL):

    SELECT   LAST_UPDATE_DATE
      FROM   po_headers_all
     WHERE   LAST_UPDATE_DATE >=to_date( '01-10-2005 00:00:00', 'MM/DD/RRRR HH24:MI:SS')

    Expecting the default date format to match your input is a certain recipe for failure. It's best to always use TO_DATE() to convert strings to valid dates and TO_CHAR() to convert valid dates to strings.
     
    StryderKC likes this.
  3. StryderKC

    StryderKC Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Thanks zargon. Of course, that was silly of me. I'll used TO_DATE and my query worked perfectly.