date Format error [message #637665] |
Thu, 21 May 2015 07:57 |
|
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 #637667 is a reply to message #637665] |
Thu, 21 May 2015 08:06 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
palpali wrote on Thu, 21 May 2015 07:57Hallo 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 #637680 is a reply to message #637678] |
Thu, 21 May 2015 09:13 |
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.
|
|
|
|