Hi @AreQ , this error is likely to occur when you supply a literal date string to Oracle that is not in the format it is expecting.
I assume that the
activdate column on your
contract table is an oracle DATE datatype.
Oracle requires that if you pass a date as a literal without using to
TO_DATE function (to state the format of the date you are supplying), that the format matches the
NLS_DATE_FORMAT for your current session. Assuming you haven’t specified NLS_DATE_FORMAT, it will probably be DD-MON-YY e.g. ‘01-JAN-22’.
If you want to, you can find out what the current expected format for a date is by simply returning the current sysdate from oracle,
SELECT SYSDATE FROM DUAL
As the date format could be modified for a given session,( using
ALTER SESSION SET NLS_DATE_FORMAT = '<date format>' ), it is best with Oracle to ALWAYS wrap date literals with the TO_DATE function, so that you are making no assumptions about the current default format.
Assuming that the date literal you have is in the format YYYY-MM-DD, you should find that changing your query to the following should work:
Select contract.id, contract.activdate
Please note that if there is a time component to your
activdate column (so that the column contains a time other than midnight, and you only wish to find dates that are AFTER 2022-01-01, then you will also need to take steps to “truncate” the date, which means it will return the date with the time set to midnight.
This would have an implication on indexing though, if there is an index on
contract.activdate, so if that is a problem you may have to look into ways to deal with this such as “functional indexes”.
As I mentioned, this is only a problem though if your activdate contains time as well.
For example, if contract.activdate contains the date/time ‘2022-01-01 23:32:00’, then it will be returned even though the actual date part is not greater than ‘2022-01-01’. This is because in Oracle it would be comparing ‘2022-01-01 23:32:00’ with ‘2022-01-01 00:00:00’ which you can see is “greater”.
By putting TRUNC around the date, the condition would then translate to
'2022-01-01 00:00:00' > '2022-01-01 00:00:00' which then means that the row would NOT be returned, and this is likely to be what you had in mind.