sql condition to not include 4712 date

Question

I always have an issue with date transformation. Can someone guide and help me understanding the date transformation.

I am using the below code in Oracle Fusion HCM Extract tool and I am getting the correct output

APPROVAL_STATUS_CD='APPROVED'
AND ABSENCE_STATUS_CD in ('SUBMITTED','ORA_WITHDRAWN')
and typetl.name != 'Banked Time - Disbursement'
and  (TO_DATE(trunc(start_date) ,'YYYY-MM-DD')
>=  TO_DATE((select trunc((sysdate),'month') as FirstDay from dual),'YYYY-MM-DD'))

but it is giving me data that has start_date as '4712-12-31' as well. I do not want this in my output. as soon as i add the below condition -

and (TO_DATE(trunc(start_date) ,'YYYY') != TO_DATE('YYYY','4712'))

I am not getting any output. How do I restrict the 4712 date in the start_Date column i.e. whichever data has 31-12-4712 in start_date should not come in output.

Answer 1

Assuming that there will be no higher values then you want:

AND start_date < DATE '4712-12-31'

Note: NEVER use TO_DATE on a value that is already a DATE data type.

Which would make your query:

WHERE  APPROVAL_STATUS_CD='APPROVED'
AND    ABSENCE_STATUS_CD in ('SUBMITTED','ORA_WITHDRAWN')
AND    typetl.name != 'Banked Time - Disbursement'
AND    start_date >=  TRUNC(SYSDATE,'MM')
AND    start_date <  DATE '4712-12-31'

Answer 2

If you don't supply all the date elements then Oracle defaults to the first day of the current month; so TO_DATE('YYYY','4712') evaluates to 4712-04-01, not 4712-12-31 or 4712-01-01.

If you want a fixed date then it's easier to use a literal: DATE '4712-12-31', or possibly - given the range of valid dates Oracle allows - you really want DATE '-4712-01-01' (or DATE '-4712-12-31'). I'd check the full actual value you have in your data with TO_CHAR(start_date, 'SYYYY-MM-DD'). That will show you if it's BC/BCE (with a negative value) or AD/CE (with a positive value).

---

Also, do not use TO_DATE() for a value that is already a date; it might work, or it might do odd things. You don't need to do that. When you do TO_DATE(trunc(start_date) ,'YYYY-MM-DD') you're implicitly doing TO_DATE(TO_CHAR(trunc(start_date), <NLS_DATE_FORMAT>) ,'YYYY-MM-DD') - which relies on the current session's NLS settings. Even if it works today, for you, it will break one day someone else.

Just trunc(start_date) and `trunc(sysdate, 'month') is enough. Though there's no point truncating the start_date really - if the truncated value is after the start of the month, so is the original non-truncated value.