Oracle Date Issue in Where Clause

Question

I am unable to get the date column to respect the where clause. Regardless what I do, it does not filter on date. I have tried all combinations of to_char and to_date in vain.

HAVING TO_CHAR(PAYMASTR.CHECK_DATE,'MM/DD/YYYY') > '01/01/2021'

I have also tried the code below with all combinations of to_char and to_date.

HAVING PAYMASTR.CHECK_DATE >= TO_DATE('01-01-2021 12:00:00 AM',
  'MM-DD-YYYY HH:MM:SS AM')

The check_date of of type DATE.

Result set:

|COMPANY|EMPLOYEE|PAY_SUM_GRP|PAY_GRADE RATE|WAGE_AMOUNT|NET_PAY_AMT|GROSS_PAY|CHECK_DATE|
|-------|--------|-----------|--------------|-----------|-----------|---------|----------|
|2|5|REG    09|21.98|175.84|1459.96|2263.19|1/19/2007 12:00:00 AM|
|2|5|REG    09|21.98|175.84|1663.93|2589.43|1/5/2007 12:00:00 AM|

Answer 1

If CHECK_DATE column's datatype is DATE (should be! If it is VARCHAR2, you're doing it wrong!), then

having check_date > date '2021-01-01'

i.e. compare date to date literal.

---

Second code you posted is almost OK:

HAVING PAYMASTR.CHECK_DATE >= TO_DATE('01-01-2021 12:00:00 AM', 'MM-DD-YYYY HH:MI:SS AM')
                                                                               --
                                                        MI for minutes; MM is for month

Answer 2

It's all about how Oracle stores and works with date DATATYPE The date has seven components Century, year, month, day, hour, minute, seconds and all these components take seven bytes of storage. Whenever you fetch a Date column from a table, the date value is formatted in a more readable form and this format is set in the nls_date_format parameter.

I am assuming you are grouping by CHECK_DATE otherwise you need to add this date filter with the WHERE clause.

So first check the datatype of your column CHECK_DATE If it is date then

HAVING CHECK_DATE >= TO_DATE('01-01-2021', 'MM-DD-YYYY')

You don't have to provide hours, minutes, and seconds if omitted hours are rounded to 12 AM or 00 if the 24-hour format is used; Or if you want to have hours as well then you used MM instead of MI for minutes.

HAVING CHECK_DATE >= TO_DATE('01-01-2021 00:00:00', 'MM-DD-YYYY HH24:MI:SS')

And this does not make sense

HAVING TO_CHAR(PAYMASTR.CHECK_DATE,'MM/DD/YYYY') > '01/01/2021'

You want to compare dates not characters and to_char will provide you a character string that has no sense of comparing with another string '01/01/2021'.

So if you are not grouping by CHECK_DATE user filter condition with WHERE clause or check the datatype of CHECK_DATE if it is not DATE change it to DATE.

Answer 3

I found this article on code project that did the trick for me. I was struggling really hard to get the query to respect the date parameter in the queru. Setting the session to NLS_DATE_FORMAT worked. Not sure what other implications it may have. Will have to talk to the DBA.

Code Project