OraFAQ Forum: SQL & PL/SQL » Date time format error : ORA-01830

Home » SQL & PL/SQL » SQL & PL/SQL » Date time format error : ORA-01830 (Oracle 10G)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Date time format error : ORA-01830 [message #576111]

Thu, 31 January 2013 10:07

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!

Report message to a moderator

Re: Date time format error : ORA-01830 [message #576112 is a reply to message #576111]

Thu, 31 January 2013 10:11

BlackSwan
Messages: 20094
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/

Report message to a moderator

Re: Date time format error : ORA-01830 [message #576117 is a reply to message #576111]

Thu, 31 January 2013 11:07

Michel Cadot
Messages: 54157
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

Report message to a moderator

Re: Date time format error : ORA-01830 [message #576164 is a reply to message #576117]

Fri, 01 February 2013 01:30

_jum
Messages: 450
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

Report message to a moderator

Re: Date time format error : ORA-01830 [message #576166 is a reply to message #576164]

Fri, 01 February 2013 01:43

Michel Cadot
Messages: 54157
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

Report message to a moderator

Re: Date time format error : ORA-01830 [message #576227 is a reply to message #576166]

Fri, 01 February 2013 09:09

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!

Report message to a moderator

Previous Topic:	Create table using trigger
Next Topic:	Tables function doubt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 08:28:36 CDT 2013

Total time taken to generate the page: 0.10279 seconds