Home » SQL & PL/SQL » SQL & PL/SQL » date Format error (sql oracle)
date Format error [message #637665] Thu, 21 May 2015 07:57 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo all

I have got a Problem in my SQL query. Can you please give me some hints or help?

select pid, month, art, used 
from table1
where to_date(month || '01', 'yyyymmdd') > sysdate;


and i am getting error:
ORA-01840: input value not long enough for date Format

Thankx in advance
Regards,
Re: date Format error [message #637666 is a reply to message #637665] Thu, 21 May 2015 08:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
What is the data type of column month? Please post a test case.

[Updated on: Thu, 21 May 2015 08:05]

Report message to a moderator

Re: date Format error [message #637667 is a reply to message #637665] Thu, 21 May 2015 08:06 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
palpali wrote on Thu, 21 May 2015 07:57
Hallo all

I have got a Problem in my SQL query. Can you please give me some hints or help?

select pid, month, art, used 
from table1
where to_date(month || '01', 'yyyymmdd') > sysdate;


and i am getting error:
ORA-01840: input value not long enough for date Format

Thankx in advance
Regards,


What is the data type of the column 'month'?
You are specifying a format mask of 8 characters. Since you are providing a literal of 2 characters, that column must resolve to a character string of exactly 6 characters.
Your format mask suggests that 'month' should actually be a 4-digit year and 2-digit month.
Don't forget that all DATEs (including sysdate) have a time component that is factored in to any comparisons.

-- Edit
ONe other thing. The very fact that you are having to append something to 'month' to crate a DATE strongly suggests that 'month' is not a DATE but a VARCHAR. That, in itself, is seriously flawed design. Dates should ALAWAYS be stored as DATE. Even your application doesn't care about certain parts, like the time, or the day of the month.

[Updated on: Thu, 21 May 2015 08:15]

Report message to a moderator

Re: date Format error [message #637669 is a reply to message #637667] Thu, 21 May 2015 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I bet for either a number or a 4 letter strings.

Re: date Format error [message #637670 is a reply to message #637669] Thu, 21 May 2015 08:25 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Please do a describe on table1 and paste it into this issue.

DESC MYTABLE
Re: date Format error [message #637673 is a reply to message #637669] Thu, 21 May 2015 08:47 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Michel Cadot wrote on Thu, 21 May 2015 14:17

I bet for either a number or a 4 letter strings.




That, or unvalidated inputs.

Which is why storing "dates" in anything other than date/timestamp type fields is heresy and you deserve all that is coming Razz
Re: date Format error [message #637675 is a reply to message #637673] Thu, 21 May 2015 08:52 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Null values will also be a problem if they're allowed
Re: date Format error [message #637678 is a reply to message #637675] Thu, 21 May 2015 09:10 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
hallo all thankx for your promt Response,
yes month Format is varchar2(6) but i did use the to_date function
anyway thankx all.. i did solve it
Smile
Re: date Format error [message #637680 is a reply to message #637678] Thu, 21 May 2015 09:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
palpali wrote on Thu, 21 May 2015 19:40

anyway thankx all.. i did solve it


Would you mind to share your solution? It would be helpful for others.

And by the way, you must fix your design first.
Re: date Format error [message #637681 is a reply to message #637678] Thu, 21 May 2015 09:13 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You also don't need the '01'. try

select to_date('201505','YYYYMM') from dual;

it automatically returns the first of the month.

Previous Topic: Error in execution of Trigger
Next Topic: string aggregation
Goto Forum:
  


Current Time: Tue Mar 19 01:18:06 CDT 2024