comparing current day of Date type with Varchar day column in Oracle

Question

I have a Varchar day column and I would like to select all rows where the day from day column matches current day in the system. I came up with this query but it didn't find any match, although today is Thursday and there is Thursday stored in the day column. I'm not sure if this is the right way to compare date data type with varchar.

select * from schedule
where day = (select to_char(sysdate,'day') from dual);

Answer 1

Your query looks reasonable to me, I would double check that to_char(sysdate, 'day') actually returns Thursday as you expect.

On a side note, you don't need to use a subquery in this case. A simpler, but equivalent query is:

select * from schedule where day = to_char(sysdate, 'day');

Answer 2

Hi Mike55,

I think the problem comes from the fact that to_char functions are space-padded by default:

SQL> SELECT '.' || to_char(SYSDATE + rownum, 'day') || '.' AS "Day"
  2    FROM dual CONNECT BY LEVEL <= 5;

Day
-----------
.friday   .
.saturday .
.sunday   .
.monday   .
.tuesday  .

To prevent padding, use the prefix fm:

SQL> select * from dual where to_char(sysdate, 'fmday')='thursday';

DUMMY
-----
X

Also note that VARCHAR comparison is case-sensitive.