Error in Oracle TO_CHAR() Function

Question

Why does the following error occur on Line 7 of the query below?

Error: ORA-01861: literal does not match format string

Query:

01: SELECT hour
02: FROM (WITH all_hours AS
03:             (SELECT TO_DATE ('2000-01-01', 'yyyy-mm-dd') 
04:                      + NUMTODSINTERVAL (LEVEL - 1, 'hour') hour
05:              FROM DUAL
06:              CONNECT BY LEVEL <= 1000000)
07:      SELECT TO_CHAR (h.hour, 'yyyy-mm-dd hh:mi am') hour
08:      FROM all_hours h
09:      GROUP BY h.hour)
10: WHERE hour BETWEEN TO_DATE ('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')
11:                AND TO_DATE ('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')

Is the time portion of the date important to what you're doing? — ChadNC
– ChadNC, Commented Nov 10, 2009 at 16:58

Dmitry Osinovskiy · Accepted Answer · 2009-11-10 17:00:02Z

6

Well, it is not on the line 7. Problem is that "hour" on some level is converted to string (to_char), but after that is compared to the date, and implicit conversion does not work always. Corrected query:

SELECT hour
FROM (WITH all_hours AS
            (
            SELECT TO_DATE ('2000-01-01', 'yyyy-mm-dd') 
                     + NUMTODSINTERVAL (LEVEL - 1, 'hour') hour
             FROM DUAL
             CONNECT BY LEVEL <= 1000000
             )
     SELECT TO_CHAR (h.hour, 'yyyy-mm-dd hh:mi am') hour, h.hour as hourdate
     FROM all_hours h
     GROUP BY h.hour)
WHERE hourdate BETWEEN TO_DATE ('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')
               AND TO_DATE ('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')

answered Nov 10, 2009 at 17:00

Dmitry Osinovskiy

10.2k1 gold badge50 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vincent Malgrat · Accepted Answer · 2009-11-10 17:00:23Z

1

you're comparing DATEs to CHARs in the WHERE clause. This should work:

SQL> SELECT TO_CHAR(hour, 'yyyy-mm-dd hh:mi am')
  2    FROM (WITH all_hours AS (SELECT TO_DATE('2000-01-01', 'yyyy-mm-dd')
  3                              + NUMTODSINTERVAL(LEVEL - 1, 'hour') hour
  4                               FROM DUAL
  5                             CONNECT BY LEVEL <= 1000000)
  6          SELECT hour FROM all_hours h GROUP BY h.hour)
  7   WHERE hour BETWEEN TO_DATE('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')
  8          AND TO_DATE('2009-11-10 01:00 am', 'yyyy-mm-dd hh:mi am')
  9  ;

TO_CHAR(HOUR,'YYYY-MM-DDHH:MIA
------------------------------
2009-11-10 01:00 am

answered Nov 10, 2009 at 17:00

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Collectives™ on Stack Overflow

Error in Oracle TO_CHAR() Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related