Filter by TO_CHAR() in the WHERE clause

Question

I'm running into some unexpected behavior, which doesn't make any sense to me.

I have a table with a TIMESTAMP(6) column.

If I execute the following query:

SELECT DISTINCT TO_CHAR(ssef.SS_TIMESTAMP, 'DAY') FROM SS_EDGE_FORECAST ssef

I get the following results:

FRIDAY
TUESDAY
SUNDAY
SATURDAY
MONDAY
THURSDAY

So far so good.

However, if I try to filter my query results by specifying a specific weekday in the WHERE clause, I get no results:

SELECT * FROM SS_EDGE_FORECAST ssef
WHERE TO_CHAR(ssef.SS_TIMESTAMP, 'DAY') = 'MONDAY'

This appears to be a direct contradiction.
Why would the TO_CHAR operation produce the correct results in the SELECT clause, but not in the WHERE clause?
Why would I be able to select something, but NOT be able to filter based on that same item?

Answer 1

The issue here is, that TO_CHAR(x,'DAY') is for Monday returning 'Monday ' with blanks in the end. According to documentation for TO_CHAR function:

The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name...

The correct result shall be returned if you do something like:

SELECT * FROM SS_EDGE_FORECAST ssef WHERE TO_CHAR(ssef.SS_TIMESTAMP, 'DAY') like 'MONDAY%'

Or even better, the TO_CHAR function has so-called format model modifiers, which you can use for removing these trailing blanks:

SELECT * FROM SS_EDGE_FORECAST ssef WHERE TO_CHAR(ssef.SS_TIMESTAMP, 'fmDAY') = 'MONDAY'

shall return desired output.

Documentation for TO_CHAR and format model modifiers here: https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00216

Answer 2

First of all, the solution is:

SELECT * FROM TEST1
WHERE  TRIM(TO_CHAR(ABC,'DAY'))  = 'MONDAY'

Trim function is added.

The reason is:

TO_CHAR(TIMESTAMP,'DAY') will return 9 character string. Apparently, there are 3 trailing blank spaces at the end of 'Monday'

Here is the test case:

DB<>Fiddle

Answer 3

Its a whitespace / trimming issue.

Try this:

SELECT DISTINCT TO_CHAR(sysdate, 'DAY') FROM dual ssef where TRIM(TO_CHAR(sysdate, 'DAY'))='TUESDAY';