Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830: date format picture ends before converting entire input string (oracle 10 g)
ORA-01830: date format picture ends before converting entire input string [message #578275] Tue, 26 February 2013 06:56 Go to next message
deepak416
Messages: 56
Registered: January 2013
Location: Pune
Member
Hi,

when i run this query i am facing date format error can you please help

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

ORA-01830: date format picture ends before converting entire input string
Re: ORA-01830: date format picture ends before converting entire input string [message #578276 is a reply to message #578275] Tue, 26 February 2013 07:05 Go to previous message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

There are three problems here:
'2013-02-1. 12.0 .10.123000000'
'YYYY-MM-DD HH.MI.SS.SSSSSS'


You have more digits in yor string than you have characters in the format mask.
Fixing that, by adding another 3 S's to the end gives:
SQL> SELECT TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSSSSS') FROM dual;
SELECT TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSSSSS') FROM dual
                                               *
ERROR at line 1:
ORA-01810: format code appears twice

because the format mask code for fractional seconds is not S, it's F. Fixing that gives:
SQL> SELECT TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.FFFFFFFFF') FROM dual;
SELECT TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.FFFFFFFFF') FROM dual
                                               *
ERROR at line 1:
ORA-01821: date format not recognized


Because dates don't hold fractional seconds, only timestamps do. Fixing that (and you only need to FF in the format mask regardless of how many fractional seconds) gives:
SQL> SELECT TO_timestamp('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.FF') FROM dual;

TO_TIMESTAMP('2013-02-1.12.0.10.123000000','YYYY-MM-DDHH.MI.SS.FF')
---------------------------------------------------------------------------
01-FEB-13 12.00.10.123000000 PM


I suggest you read up on date formats in the documentation
Previous Topic: find difference in two schemas
Next Topic: error in creating DBMS_SCHEDULER job
Goto Forum:
  


Current Time: Tue Sep 16 08:48:11 CDT 2014

Total time taken to generate the page: 0.38901 seconds