Tiago do Canto Tiago do Canto - 9 days ago 5
SQL Question

ORA-01847 Error to get a date

I'm having a problem with this query:

select to_char(ADD_MONTHS((LAST_DAY(to_date(DATEVAR, 'yyyymmdd'))+1),-1),'yyyymmdd') from mytable
where ROWNUM=1
and var = to_char(last_day(to_date(DATEVAR, 'yyyyMMdd')), 'WW')-1;


I want it to return when the var is the last week number of the month and return null when it isn't if my var is '29' (weekNumber) i get:


ORA-01847: "day of month must be between 1 and last day of month"


But if i change the week number to '30'(last week number of the month) i get the correct result:

'20160701'


Please help thank you.

Answer

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.

Comments