Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Error: ORA-01830: date format picture ends before converting entire input string

    Hello

    I am fairly new to Oracle and am looking for some advice as to where I should place a CASE WHEN statement

    The entire SQL is:

    SELECT
    PART1.NUMBER,
    PART1.INTO_RECOVERY_DATE,
    PART1.LEFT_RECOVERY_DATE,
    PART1.INTO_RECOVERY_DATE_TIME,
    PART1.LEFT_RECOVERY_DATE_TIME,
    PART1.INTO_RECOVERY,
    PART1.LEFT_RECOVERY,
    PART1.THEATRE_GROUP,
    PART1.SESSION_TESTING|| ' '|| TIMEGROUP.TIME_GROUP_STARTED AS TIME_GROUP_STARTED1,
    PART1.SESSION_TESTING || ' ' || TIMEGROUP.TIME_GROUP_ENDED AS TIME_GROUP_ENDED1,

    CASE WHEN PART1.INTO_RECOVERY_DATE_TIME BETWEEN TO_DATE(TO_CHAR(PART1.SESSION_TESTING, 'DD-MON-YYYY') || ' '|| TIMEGROUP.TIME_GROUP_STARTED, 'DD-MON-YYYY HH24:MI') AND
    TO_DATE(TO_CHAR(PART1.SESSION_TESTING, 'DD-MON-YYYY') || ' ' || TIMEGROUP.TIME_GROUP_ENDED, 'DD-MON-YYYY HH24:MI') THEN 1
    WHEN PART1.INTO_RECOVERY_DATE_TIME <= TO_DATE(TO_CHAR(PART1.SESSION_TESTING, 'DD-MON-YYYY') || ' '|| TIMEGROUP.TIME_GROUP_STARTED, 'DD-MON-YYYY HH24:MI') AND PART1.LEFT_RECOVERY_DATE_TIME >= TO_DATE(TO_CHAR(PART1.SESSION_TESTING, 'DD-MON-YYYY') || ' '|| TIMEGROUP.TIME_GROUP_STARTED, 'DD-MON-YYYY HH24:MI') THEN 1 ELSE 0 END



    FROM(
    SELECT
    INTO_RECOVERY_DATE,
    LEFT_RECOVERY_DATE,
    CFIS_DATA.GRS.INTO_RECOVERY,
    CFIS_DATA.GRS.LEFT_RECOVERY,
    CFIS_DATA.GRS.THEATRE_GROUP,
    CFIS_DATA.GRS.NUMBER,
    CFIS_DATA.GRS.SESS_DATE,
    SESSION_TESTING,
    TO_DATE(TO_CHAR(CFIS_DATA.GRS.SESS_DATE, 'DD-MON-YYYY') || ' '|| CFIS_DATA.GRS.INTO_RECOVERY, 'DD-MON-YYYY HH24:MI') AS INTO_RECOVERY_DATE_TIME,
    CASE
    WHEN CFIS_DATA.GRS.LEFT_RECOVERY < CFIS_DATA.GRS.INTO_RECOVERY
    THEN TO_DATE(TO_CHAR(CFIS_DATA.GRS.SESS_DATE + 1, 'DD-MON-YYYY')|| ' '|| CFIS_DATA.GRS.LEFT_RECOVERY, 'DD-MON-YYYY HH24:MI')
    ELSE TO_DATE(TO_CHAR(CFIS_DATA.GRS.SESS_DATE, 'DD-MON-YYYY') || ' '|| CFIS_DATA.GRS.LEFT_RECOVERY, 'DD-MON-YYYY HH24:MI')
    END AS LEFT_RECOVERY_DATE_TIME,
    CASE WHEN SESSION_TESTING BETWEEN INTO_RECOVERY_DATE AND LEFT_RECOVERY_DATE THEN 1 ELSE 0 END AS DAYCHECK
    FROM CFIS_DATA.GRS,


    (SELECT SESS_DATE AS SESSION_TESTING
    FROM CFIS_DATA.GRS
    GROUP BY SESS_DATE) SESSION_DATE,


    (SELECT
    CFIS_DATA.GRS.SESS_DATE AS INTO_RECOVERY_DATE,
    CASE
    WHEN CFIS_DATA.GRS.LEFT_RECOVERY < CFIS_DATA.GRS.INTO_RECOVERY
    THEN CFIS_DATA.GRS.SESS_DATE + 1
    ELSE CFIS_DATA.GRS.SESS_DATE END AS LEFT_RECOVERY_DATE
    FROM CFIS_DATA.GRS
    WHERE CFIS_DATA.GRS.NUMBER = '0000000') CHECKING
    WHERE CFIS_DATA.GRS.NUMBER = '000000'
    AND CASE WHEN SESSION_TESTING BETWEEN INTO_RECOVERY_DATE AND LEFT_RECOVERY_DATE THEN 1 ELSE 0 END = 1) PART1,
    TIMEGROUP

    The part which I am struggling with is the part in Red. It errors when I place the statement in the select clause.

    I do not necessarily need to see the result, I am happy yo put it in a WHERE clause, but am stumped as to where to place it

    Is anyone able to help?
    Last edited by Helen Pippard; 04-03-12 at 12:54.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,817
    Please read: http://www.dbforums.com/oracle/10316...s-posters.html

    Especially the part about formatting SQL statements using [code] tags

  3. #3
    Join Date
    Mar 2007
    Posts
    615
    It looks like data in TIMEGROUP.TIME_GROUP_STARTED/TIME_GROUP_ENDED column(s) do not match 'HH24:MI' format - some of them are longer.

    You may easily reproduce it in SQL:
    Code:
    SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
    
    Session altered.
    
    SQL> select to_date( '03-APR-2012 11', 'DD-MON-YYYY HH24:MI' ) from dual;
    
    TO_DATE('03-APR-201
    -------------------
    2012-04-03 11:00:00
    
    1 row selected.
    
    SQL> select to_date( '03-APR-2012 11:550', 'DD-MON-YYYY HH24:MI' ) from dual;
    select to_date( '03-APR-2012 11:550', 'DD-MON-YYYY HH24:MI' ) from dual
                    *
    ERROR at line 1:
    ORA-01830: date format picture ends before converting entire input string
    Maybe it would be useful to fix values in that column. Or, better, use INTERVAL DAY TO SECOND data type to avoid storing invalid values there.

Posting Permissions

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