NLS_SESSION_PARAMETERS:
SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
returns
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
2 tables: BALANCE (pk: BAL_ID, fk: DT_NBR) AND DATE_TABLE (pk: ZERO_BAL_DT_NBR, column: DATE_STR(varchar2))
DATE_STR is varchar2: 'MM/DD/YYYY'
SELECT TO_DATE(SUBSTR(SYSDATE,1,10),'YYYY/MM/DD') SYS_DATE
, TO_DATE(d.DATE_STR,'MM/DD/YYYY') ZERO_BALANCE_DATE
, TO_DATE(SUBSTR(SYSDATE,1,10),'YYYY/MM/DD') - TO_DATE(d.DATE_STR,'MM/DD/YYYY') DATE_DIFF
FROM BALANCE b, DATE_TABLE d
WHERE d.DT_NBR = b.ZERO_BAL_DT_NBR
AND LENGTH(d.DATE_STR) > 0
AND (TO_DATE(SUBSTR(SYSDATE,1,10),'YYYY/MM/DD') - TO_DATE(d.DATE_STR,'MM/DD/YYYY')) < 40
;
Or
SELECT TO_DATE(SUBSTR(SYSDATE,1,10),'YYYY/MM/DD') SYS_DATE
, TO_DATE(d.DATE_STR,'MM/DD/YYYY') ZERO_BALANCE_DATE
, SYSDATE - TO_DATE(d.DATE_STR,'MM/DD/YYYY') DATE_DIFF
FROM BALANCE b, DATE_TABLE d
WHERE d.DT_NBR = b.ZERO_BAL_DT_NBR
AND LENGTH(d.DATE_STR) > 0
AND SYSDATE - TO_DATE(d.DATE_STR,'MM/DD/YYYY') < 40;
I tried one million methods to convert date formats. Nothing worked well so far.
Error:
ORA-01839: date not valid for month specified
01839. 00000 - "date not valid for month specified"
*Cause:
*Action:
What is wrong in my query? Basically, I need to query records that are no more than 40 days old when [ZERO_BAL_DT_NBR] has a value.
The query works well as long as I add the where clause "(SYSDATE - TO_DATE(d.DATE_STR,'MM/DD/YYYY')) < 40", the query won't work.