Prev
Next
Forums Smart Notification

SQL Date Syntax

Sort:
You are not authorized to post a reply.
Author
Messages
Ken Ptak
Basic Member
Posts: 20
Basic Member
11/14/2007 3:07 PM
    Hello - I have a query that creates an Infoset using a JDBC connection successfully. I want to limit the records being returned by the Infoset where the PER_END_DATE is after July 1, 2007.

    I receive the error “Error: ORA-01843: not a valid month”
    When I try to add the following criteria to the SQL statement:
    HAVING ((GMDISTRIB.PER_END_DATE)>'07/01/2007')

    I’ve tried to play with the formatting of the date but I haven’t found the correct format…
    Any suggestions/ideas?
    Thanks,
    Ken
    Will
    Veteran Member
    Posts: 39
    Veteran Member
    11/14/2007 3:23 PM
      Hi Ken,

      You need to cast those strings you are passing in the HAVING statement to something SQL can understand and use comparison (>) with.

      HAVING (CAST(GMDISTRIB.PER_END_DATE AS datetime)>CAST('07/01/2007' AS datetime))
      should work. Now you are comparing a date object with a date object.

      Guillaume
      Ken Ptak
      Basic Member
      Posts: 20
      Basic Member
      11/14/2007 4:12 PM
        That makes sense, but it didn't quite work...

        When I enered:
        HAVING (CAST(GMDISTRIB.PER_END_DATE AS datetime)>CAST('07/01/2007' AS datetime))

        I received the error:
        Error: ORA-00902: invalid datatype

        So I modified the query to read:
        HAVING (CAST(GMDISTRIB.PER_END_DATE AS date)>CAST('07/01/2007' AS date))
        Which removes the 'Invalid datatype' error message but I still get the original message:
        Error: ORA-01843: not a valid month

        Any other ideas? Thanks in advance!
        Will
        Veteran Member
        Posts: 39
        Veteran Member
        11/14/2007 5:17 PM
          Oh, we are talking Oracle here, sorry I did not catch it.

          What format is GMDISTRIB.PER_END_DATE in your table?
          John Henley
          Senior Member
          Posts: 3348
          Senior Member
          11/14/2007 5:39 PM
            Ken, since this is Oracle, you probably need to make sure you're entering the dates in the (default) format defined in the database, which is usually dd-MMM-ccyy (I think that is it--off the top of my head). Generally with Oracle, I avoid that altogether by using the to_date() function:
            HAVING (GMDISTRIB.PER_END_DATE>to_date('2007-07-01','yyyy-mm-dd'))
            Thanks for using the LawsonGuru.com forums!
            John
            Ken Ptak
            Basic Member
            Posts: 20
            Basic Member
            11/14/2007 6:24 PM
              The to_date() function worked beautifully. Thanks for your help Guillaume and John!
              You are not authorized to post a reply.