I have a date field CONTACT_DATE in a DATE format (2000-07-28 00:00:00.000). I want to add a WHERE clause to a query to return results where the CONTACT_DATE is within 6 months of the current date, as well as truncate it down to just the date value (YYYY-MM-DD).
Through research, I came up with a best guess on the code:
WHERE TO_CHAR(P.CONTACT_DATE, 'YYYY-MM-DD') >= TO_DATE(SUBSTR(LAST_DAY(ADD_MONTHS(SYSDATE, -6)),1,10),'YYYY-MM-DD')
It's throwing the ORA-01861 error message, but that doesn't even validate whether or not my attempt at the code is correct. So that's essentially my question: is the code correct, and if not, where am I going wrong?
There are two parts to this: the WHERE filter, and the SELECT output.
For the filter, thanks to cultural/internationalization issues, converting to string to narrow your data is almost always the slowest and least accurate way to do this, and it's not close. Specifically, there is a HUGE difference between a VARCHAR column formatted to look like a date, and a real DATE column, where the actual format is binary and the 2000-07-28 00:00:00.000 value you see is a convenience provided by your tooling.
Additionally, you want to build your conditional expressions for the WHERE filter in a way that avoids modifying the stored data at all. This helps ensure any indexes on the column remain valid for this query, which can have a HUGE impact on performance, and avoid needing to do expensive mutations for the value on every row in the table (even those you won't need).
One thing to note that helps with this is if the truncated version of P.CONTACT_DATE is greater than your desired value, the untruncated value is logically also greater than the desired value, so the extra truncation step is not needed at this point for that side of the expression.
Put it together, and you end up with a conditional expression like this:
WHERE P.CONTACT_DATE >= ADD_MONTHS(TRUNC(SYSDATE), -6)
This code is much shorter and easier to read, avoids potential string formatting issues, and is likely significantly faster than the original.
I left out the LAST_DAY() call because it was not part of the question description, but it's easy to add if you need it.
The other part of this is the SELECT output. It's worth noting here that the best option is usually to just return the value as it is, and let the client code or reporting tool handle the format.
But I do understand sometimes there are other concerns, and you really do need to adjust things in the database first. In that case, the next best option is to still return a DATE value, rather than a string, but now ensure it's just the date (no or zeroed-out time):
SELECT TRUNC(p.CONTACT_DATE) As CONTACT_DATE
and if you really do want a string rather than a real date value:
SELECT TO_CHAR(P.CONTACT_DATE, 'YYYY-MM-DD') As CONTACT_DATE