ORA-01849: hour must be between 1 and 12

Let's break this down a bit:

WHERE (ResTRRequest.RequestTime
  BETWEEN TO_CHAR(TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH')), 'YYYY-MM-DD HH24:MI:SS') 
     AND TO_CHAR(TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '59:59' MINUTE TO SECOND, 'YYYY-MM-DD HH24:MI:SS'))

In the first place, I don't think you mean this: TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH')), I think maybe you mean this: TRUNC(TO_DATE('2012-12-01 20:10:10'), 'HH'). The number from 1-12 error comes from the fact that you have an hour of 20 and are trying to convert it into a date with the mask of HH. But as I said I think that's a typo. You can also use a TIMESTAMP literal here rather than TO_DATE():

TRUNC(TIMESTAMP'2012-12-01 20:10:10', 'HH')

Second, and just to get this out of the way, are you storing dates or timestamps as strings? That's not a good idea.

Third, it's not a good idea to use BETWEEN in date comparisons because you can miss the edge cases. It might be better to rewrite this as follows:

WHERE ( ResTRRequest.RequestTime >= TO_CHAR(TRUNC(TO_DATE('2012-12-01 20:10:10'), 'HH'), 'YYYY-MM-DD HH24:MI:SS') 
    AND ResTRRequest.RequestTime < TO_CHAR(TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') )

Assuming that ResTRRequest.RequestTime is of a date type, this Where clause will work:

  where ResTRRequest.RequestTime
BETWEEN TRUNC(TO_DATE('2015-02-26 20:10:10', 'YYYY-MM-DD HH24:MI:SS'), 'HH')
    AND TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '59:59' MINUTE TO SECOND

If you have to compare character representations, keep in mind that you compare in lexicographic order, meaning that prefixes of strings are sorted before their strings! Avoid ensueing complications by using identical formatting models with componnents arranged in the order of decreasing significance. E.g.

 TO_CHAR(<whatever>, 'YYYY-MM-DD HH24:MI:SS')

but not

 TO_CHAR(<whatever>, 'MM/DD/YYYY HH24:MI:SS')

If the language setting on Oracle is set for using the 12 hours time, this problem will occur when converting the 24 hours time format. There are two solutions to this :

  1. Convert TIMESTAMP/DATE format in Oracle client

    alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';

  2. Convert query to match 24hr format

    SELECT * FROM TEST_ WHERE DOB > TRUNC(TIMESTAMP'1970-01-01 20:10:10', 'HH'); or

    SELECT * FROM TEST_ WHERE DOB > to_date('1970-01-01 20:00:00','YYYY-MM-DD HH24:MI:SS');


Problem is in mask:

TO_DATE('2012-12-01 20:10:10', 'HH')

Replace with this one:

TO_DATE('2012-12-01 20:10:10', 'HH24')

Tags:

Oracle

Recent Posts