Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Unanswered: ORA-01849 Timestamp format issue

    Has anyone here had any dealings with queries that use a timestamp in a where clause hitting an Oracle 9i database? The problem I am having is that I can’t seem to find a format for the date or timestamp that Oracle is happy is with. I have used java.sql.Timestamp, java.sql.Date, java.util.Date, Java.util.Calendar.getTime(). The oracle Timestamp format has been set to 'yyyy-mm-dd hh:MM:ss.fff' and that is now how Timestamp elements appear in the database, but I still can't seem to get oracle to like my query timestamp format.

    The following generates an ORA-01849 Hour must be between 1 -12

    select * from foo where myDate >= ’2004-01-12 00:00:00.000’ and myDate <= ’2004-01-12 10:10:00.000’

    Note that the hour is cetainly less than 12, so I am at a loss at what format Oracle is expecting. java.sql.Timestamp seems to match the format, yet.....

    Any way, if anyone has tackled this problem and has a solution I am all ears.



  2. #2
    Join Date
    Jan 2004
    North Haven, CT
    The error was caused by the system is using 12 hr time format (HH) and
    the specified hours must be between 1 and 12.

    Why don't you try

    select * from foo where myDate >= to_date(’2004-01-12 00:00:00’ ,'yyyy-mm-dd hh24:mi:ss') and
    <= to_date(’2004-01-12 10:10:00’ ,'yyyy-mm-dd hh24:mi:ss') ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts