Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

OK guys, first of all, I have checked many websites about this error and, unfortunately, none of them helped me. I have the simple following query:

select * from (   
       select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew, 
              cal.daytype as type
       from vw_calendar cal)
where datew > sysdate;
When I try to execute the entire query, this error shows up:
  
ORA-01839: date not valid for month specified
If I execute only the query:
select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew, 
       cal.daytype as type
from vw_calendar cal;
It worked absolutely fine. If you want to view the results of the query: http://pastebin.com/PV95g3ac
I checked the days of the month like day 31 or leap year and everything seems correct. I don't know what to do anymore. My database is a Oracle 10g and I tried to execute the query on SQL Developer and PL/SQL Developer. Same error on both IDE.
                How many rows are in vw_calendar? Your pastebin is missing 6 dates: 2014-03-05, 2014-06-12, 2014-06-17, 2014-06-23, 2014-07-04 and 2014-07-08. If you think you should have all dates then maybe figure out why those six aren't shown - I'd suspect there are invalid values instead. Your client seems to be forgiving of the simpler query but is maybe hiding the errors (?). If it's a view can you show the definition for it; and can you show the data types?
– Alex Poole
                Sep 16, 2014 at 15:20
                There are 4377 rows (I used select count(*) from vw_calendar). I didn't notice these dates are missing, I will look into it, but I guess the missing are not related with my issue. Anyway, I've made another pastebin with june 2014 dates: pastebin.com/imKQTHwP. About the datatypes, it's hard to me because this view is just a wrapper to another view that comes from ERP (JDE Edwards Enterprise One).
– humungs
                Sep 16, 2014 at 16:36
                You can just describe vw_calendar. From the new pastebin they seem to be varchar2 fields but confirming that, and the length, might be useful. Although I still can't think of anything that would let one query run but not the other; except something being pushed into the view, but it's not like sysdate can be broken into year/month/day automatically. Have you tried filtering the inner query on year to see if the problem can be narrowed down?
– Alex Poole
                Sep 16, 2014 at 16:47
                Thanks for the useful tip about describe. Here the datatypes: pastebin.com/fndMJX8K. I tried the inner query with where cal.year = 2014 and  where cal.year = 2014 and cal.month = 06; and it worked fine. I tried with other years like 2012 and 2013. Everything running. However, if I remove where dataw > sysdate from query, it works too.
– humungs
                Sep 16, 2014 at 18:52
Actually the issue is some months have 30 days and some have 31, so the query which you are forming, it is trying to get 31st day of that month which is invalid and hence the error. For Example:
it may be trying for date like: 31-NOV-2016 which is not correct, NOV has only 30 days.
Well, I found a workaround, not a solution. If anyone knows the "correct" solution for this problem, I appreciate if you share with us. My "solution" convert the date to number and then compare with a sysdate (converted too). Take a look:
select * from 
       ( select to_number(cal.year||cal.month||cal.day) as datew, 
                cal.daytype as type from vw_calendar cal ) a 
where a.datew > to_number(to_char(sysdate, 'yyyymmdd'));
Thanks to everyone!
select * from (   
       select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew, 
              cal.daytype as type
       from vw_calendar cal) a
where a.datew > sysdate;
                While it's certainly good practice to use alias names for nested queries, how is this relevant to the original question?
– Frank Schmitt
                Sep 17, 2014 at 12:29
                Sorry, but the same error occurred. I tried change the "where part" removing sysdate and putting to_date(). But no success. My code: where mycal.data > to_date('20140101', 'yyyymmdd');
– humungs
                Sep 17, 2014 at 13:20
                @RicardoGiaviti  select * from (           select to_date(cal.year||cal.month||cal.day,'yyyymmdd') as datew,cal.daytype as type                      from vw_calendar cal ) a where trunc(a.datew) > trunc(sysdate);
– Vignesh Kumaresan
                Sep 17, 2014 at 18:07
                @VigneshKumaresan same error too. This error is freaking me out. I am considering open a ticket on Oracle.
– humungs
                Sep 17, 2014 at 18:41
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.