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

    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.


    Thanks,

    Lorien

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    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
  •