Home » SQL & PL/SQL » SQL & PL/SQL » Date time format error : ORA-01830 (Oracle 10G)
Date time format error : ORA-01830 [message #576111] Thu, 31 January 2013 10:07 Go to next message
utkarsh783
Messages: 2
Registered: January 2013
Junior Member
I am running following statement

select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5

and It gives me the error

ORA-01830: date format picture ends before converting entire input string
01830. 00000 - "date format picture ends before converting entire input string"

Any idea? Thank you all!
Re: Date time format error : ORA-01830 [message #576112 is a reply to message #576111] Thu, 31 January 2013 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
what datatype is (B.EOAWDTTM_MODIFIED)?
what is content of (B.EOAWDTTM_MODIFIED)

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Date time format error : ORA-01830 [message #576117 is a reply to message #576111] Thu, 31 January 2013 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Always use SQL*Plus and copy and paste your session, the WHOLE session, so we are able to see and reproduce what you did.

Regards
Michel

Re: Date time format error : ORA-01830 [message #576164 is a reply to message #576117] Fri, 01 February 2013 01:30 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
As the error message says: "date format picture ends before converting entire input string"
Try:
select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5
Re: Date time format error : ORA-01830 [message #576166 is a reply to message #576164] Fri, 01 February 2013 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong if EOAWDTTM_MODIFIED is a VARCHAR2 or a NUMBER or...
You don't know the basics to give a solution, so don't post silly things especially when there is surely better ways to write it depending on the purpose of the query (which you don't know either).

Regards
Michel
Re: Date time format error : ORA-01830 [message #576227 is a reply to message #576166] Fri, 01 February 2013 09:09 Go to previous message
utkarsh783
Messages: 2
Registered: January 2013
Junior Member
Thank you guys it was format issue between two columns, DateTM& Date so I changed the format and it works well now.
Appreciate your help. Thanks again!
Previous Topic: Create table using trigger
Next Topic: Tables function doubt
Goto Forum:
  


Current Time: Wed Apr 16 14:25:15 CDT 2014

Total time taken to generate the page: 0.14022 seconds