OraFAQ Forum: SQL & PL/SQL » Ora-01840 input value not long enough for date format

Home » SQL & PL/SQL » SQL & PL/SQL » Ora-01840 input value not long enough for date format

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

Ora-01840 input value not long enough for date format [message #139329]

Tue, 27 September 2005 09:00

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

I am trying to run a report between two dates, but certain dates give the ora-01840 error message. eg between 01-APR-2005 and 23-SEP-2005 gives the error message but between 01-SEP-2005 and 25-SEP-2005 does not.

The date is in DD-MON-YYYY format.

Any input, anyone?

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139334 is a reply to message #139329]

Tue, 27 September 2005 09:27

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Is there any other code which deals with these dates?

By
Vamsi.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139354 is a reply to message #139334]

Tue, 27 September 2005 10:53

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

I have attached the code for you. It's kinda long but you could look at only the sections with the date format, I guess.

Thanks

Attachment: date code.txt
(Size: 2.66KB, Downloaded 3080 times)

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139439 is a reply to message #139354]

Wed, 28 September 2005 00:51

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Hi,
Problem is there in the following code.

'TO_DATE(LPAD(TO_CHAR(tp.fisc_yr_end),4,''0'')||TO_CHAR(tper.tper_year),''DDMMYYYY'')'

For example if you run this code you will get ORA-01840 error.

SELECT TO_DATE('0101','DDMMYYYY') 
FROM dual;

means some where tper.tper_year is becoming NULL.

I didn't understand the columns tp.fisc_yr_end and tper.tper_year.
As the format you specified the first 4 digits should be DDMM. But for tp.fisc_yr_end you are padding with 0's like a year.

By
Vamsi.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139530 is a reply to message #139439]

Wed, 28 September 2005 09:11

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

I don't understand. SO what should the correct code be for what's below:

'TO_DATE(LPAD(TO_CHAR(tp.fisc_yr_end),4,''0'')||TO_CHAR(tper.tper_year),''DDMMYYYY'')'

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139542 is a reply to message #139530]

Wed, 28 September 2005 10:32

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Let me know what the columns tp.fisc_yr_end and tper.tper_year for.

Some data also.

By
Vamsi.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139602 is a reply to message #139542]

Wed, 28 September 2005 15:26

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

tp.fisc_yr_end is to determine what the fiscal year (not calender year) for businesses are, and tper.tper_year are for calender year for individuals. I hope this helps.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #139640 is a reply to message #139602]

Thu, 29 September 2005 00:20

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

So will tp.fisc_yr_end be in DDMM format and tper.tper_year be in YYYY format?

Provide some distinct values for those columns in the tables tp and tper.

Give the discription of the tables also.

By
Vamsi.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #140085 is a reply to message #139640]

Fri, 30 September 2005 14:40

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

FISC_YR_END NOT NULL NUMBER(4)

TPER_YEAR NOT NULL NUMBER(4)

Yes fsc_yr_end should be DDMM and tper_year YYYY.

My report was able to run for 29-SEP-2005 but not for
30-SEP-2005. I am so confused. What's the difference?

HELP!!!

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #140244 is a reply to message #140085]

Mon, 03 October 2005 02:40

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Might be TPER_YEAR is NULL for that date.
Check it out.

if so, put nvl there.

By
Vamsi.

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #140523 is a reply to message #140244]

Tue, 04 October 2005 10:49

sweetgeegee27
Messages: 107
Registered: June 2005

Senior Member

So what value should I assign for the NVL function for tper, when it's null?

NVL(TO_CHAR(tper.tper_year),???)

Report message to a moderator

Re: Ora-01840 input value not long enough for date format [message #140639 is a reply to message #140523]

Wed, 05 October 2005 01:09

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I can't predict that one.

It is depending on your functionality and logic.

You may give current year.

By
Vamsi.

Report message to a moderator

Previous Topic:	Automating trigger
Next Topic:	Query to merge records based on time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:49:43 CDT 2024