I'm having a problem with this query:
select to_char(ADD_MONTHS((LAST_DAY(to_date(DATEVAR, 'yyyymmdd'))+1),-1),'yyyymmdd') from mytable
and var = to_char(last_day(to_date(DATEVAR, 'yyyyMMdd')), 'WW')-1;
ORA-01847: "day of month must be between 1 and last day of month"
I don't see an issue with your code? With var set to 30:
WITH mytable AS (SELECT trunc(SYSDATE, 'yyyy') - 1 + LEVEL datevar FROM dual CONNECT BY LEVEL <= 366) SELECT to_char(add_months((last_day(datevar) + 1), -1), 'yyyymmdd') ret_col FROM mytable WHERE rownum = 1 AND 30 = to_number(to_char(last_day(datevar), 'WW'))- 1; RET_COL -------- 20160701
Change the 30 to a 29 in the above query, and I get no rows returned.
The only thing that I can think is that your datevar column has a datatype of VARCHAR2 instead of DATE and that you're experiencing some implicit date conversion issues due to your NLS_DATE_FORMAT setting.
N.B. edited my query to remove the to_date()'s that were there on the datevar (which, as I'd defined the datevars as DATEs, was a very silly thing to do! Thanks @mathguy.) Also, added in an explicit to_number() in the where clause.