If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Error: ORA-01830: date format picture ends before converting entire input string

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2011
Posts: 73
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,779
Please read: Guidance and resources for posters

Especially the part about formatting SQL statements using [code] tags
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 612
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On