Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Question Unanswered: ORA-01849 Hour must be between 1 and 12 error

    Hello,

    I have problem with the error shown in the title,
    I created table msrmnts with columns
    ID - VARCHAR2(30 BYTE)
    date_time - VARCHAR2(30 BYTE)
    value - FLOAT
    TYPE_ID - varchar2(30 BYTE)

    When I issue SQL command

    SELECT value, date_time from msrmnts WHERE TYPE_ID='v9' AND date_time>=to_timestamp('01/01/2009', 'DD/MM/YYYY HH24:MIS')
    sqlplus sais

    ERROR at line 1:
    ORA-01849: hour must be between 1 and 12

    I tried to change HH24 to HH12 and HH, also changing system time format on database and connecting machine but nothing helps.
    I have oracle 10g installed.

    Any idea or help.
    Thanks.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    to_timestamp('01/01/2009', 'DD/MM/YYYY HH24:MIS')

    either add time to your first argument or remove the time element from your second argument.
    You will also have to convert your date_time column using to_timestamp.
    (And slap whoever decided to store dates in varchar columns!)

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Many things wrong here...
    Firstly, your table definition defines the date_time column as a varchar2. That is absolutely evil. You are going to have continuous problems with comparing dates, not to mention the possibility of putty bogus data into your date_time column. The rule here is to always use the right data type i.e. use "date" datatype for this column.

    Edit: This following paragraph is not correct (my appologies). But it's close... Oracle attempts to convert the VARCHAR2 (DATE_TIME column) into a TIMESTAMP... See my better response in my next post further down
    Furthermore you are attempting to compare your varchar2 column with a timestamp value
    date_time>=to_timestamp('01/01/2009', 'DD/MM/YYYY HH24:MIS'). Oracle will implicitly convert your timestamp to a varchar2 and then compare the value to the column. You are going to have totally wrong results depending on your NLS_* parameters.

    Edit: Again, not true... You can use TO_TIMESTAMP with input text that is shorter than the format mask. I.e. TO_TIMESTAMP('01/01/2009', 'DD/MM/YYYY HH24:MIS') is perfectly legal. See my usage of this in my next post further down.
    The second thing here is that your string input to the to_timestamp function must match the date format string supplied exactly. So in your example, you would have to use:
    to_timestamp('01/01/2009 00:00:00', 'DD/MM/YYYY HH24:MIS')
    or
    to_timestamp('01/01/2009', 'DD/MM/YYYY')

    Thirdly, a timestamp is used when you require a date value with precision down to the microsecond. If you only require precision to the second, then rather use to_date.
    Last edited by dayneo; 12-18-09 at 08:45. Reason: Correction required on untrue facts

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Thanks on advice,
    I'll try to solve these issues, and to slap database creator (not me.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    qaswed,

    My apologies, I should have provided proof for some of the rubbish that I was telling you. Let me try again. This time, I will provide proofs.

    "your table definition defines the date_time column as a varchar2"
    This statement is correct and is the entire source of your problem. When you compare the DATE_TIME column to the TO_TIMESTAMP, Oracle implicitly attempts to convert the DATE_TIME column from a VARCHAR2 datatype into a TIMESTAMP datatype. It does this using your NLS_* parameter and of course your NLS_TIMESTAMP_FORMAT does not match the format of the data in the DATE_TIME column.

    The following screen capture from my test database proving the point.
    Code:
    testuser@SANDBOX> CREATE TABLE MSRMNTS
      2  (
      3     ID        VARCHAR2(30 BYTE),
      4     DATE_TIME VARCHAR2(30 BYTE),
      5     VALUE     FLOAT,
      6     TYPE_ID   varchar2(30 BYTE)
      7  )
      8  /
    
    Table created.
    
    testuser@SANDBOX> INSERT INTO MSRMNTS(ID, DATE_TIME, VALUE) VALUES('1', '01/01/2009', 1);
    
    1 row created.
    
    testuser@SANDBOX> COMMIT;
    
    Commit complete.
    
    testuser@SANDBOX> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_FORMAT'
      2  /
    
    PARAMETER
    ----------------------------------------------------------------
    VALUE
    ----------------------------------------------------------------
    NLS_TIMESTAMP_FORMAT
    DD/MON/RR
    
    
    testuser@SANDBOX> SELECT VALUE, DATE_TIME FROM MSRMNTS WHERE DATE_TIME >= TO_TIMESTAMP('01/01/2009', 'DD/MM/YYYY HH24:MI:SS')
      2  /
    SELECT VALUE, DATE_TIME FROM MSRMNTS WHERE DATE_TIME >= TO_TIMESTAMP('01/01/2009
    ', 'DD/MM/YYYY HH24:MI:SS')
                                               *
    ERROR at line 1:
    ORA-01843: not a valid month
    
    
    testuser@SANDBOX> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD/MM/YYYY'
      2  /
    
    Session altered.
    
    testuser@SANDBOX> SELECT VALUE, DATE_TIME FROM MSRMNTS WHERE DATE_TIME >= TO_TIMESTAMP('01/01/2009', 'DD/MM/YYYY HH24:MI:SS')
      2  /
    
         VALUE DATE_TIME
    ---------- ------------------------------
             1 01/01/2009
    
    testuser@SANDBOX>
    In the example above, I create your table and populate it with one record. The date_time column is populate with a value of '01/01/2009' which is in format 'dd/mm/yyyy'.

    I then check the NLS_TIMESTAMP_FORMAT and found mine set to 'DD/MON/RR' (yours may be different, but it is important that it is currently not the same as my date_time value format).

    I then attempt to run your SQL, but receive the ORA-01843 that you did.

    I then change the NLS_TIMESTAMP_FORMAT to 'DD/MM/YYYY' and rerun the same SQL.

    This time, I get a result.

    Conclusion:
    The fact that your date_time column is not an Oracle DATE or TIMESTAMP datatype is going to cause you much headache in future. Trust me, I see it time and time and time again. Unpredictable errors occur, data gets corrupted and it is slower than just using the right thing to begin with.

Tags for this Thread

Posting Permissions

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