Strange to_char(sysdate,'day') conversation value

Today problem comes to me when a client ask to run some processes on workdays only. Because I do not use scheduler, but pure jobs which cannot support such a values in interval value, the only solution was to implement some "IF code" in job source. This "IF code" will act as scheduler definition.

The problem

To be sure that "day" values are always correct, at the begging I set nls_date_language. Here is the simplified code that describe main point.

SQL> set serveroutput on size 123456;
SQL> declare
  2    day_name VARCHAR2(64);
  3  begin
  4    execute immediate ('alter session set nls_date_language=' ||'''ENGLISH''');
  5    
  6    day_name := to_char(sysdate,'day');
  7    dbms_output.put_line(day_name);
  8    
  9    IF day_name in ('monday', 'tuesday','wednesday','thursday','friday') THEN
 10      dbms_output.put_line('Day is OK');     
 11    ELSE
 12      dbms_output.put_line('Day is not OK!');     
 13    END IF;
 14  end;
 15  /
tuesday
Day is not OK!

PL/SQL procedure successfully completed.

SQL> 

Look in highlighted code. dbms_output shows that return day_name seems to be "tuesday", but "IF code" was not executed as expected ("Day is not OK!" message is shown). Seems OK but it is not! What is the problem?

The solution

As I think for a while I decide to surround result value of day_name. Here is the same code again, with modification in highlighted line:

SQL> set serveroutput on size 123456;
SQL> declare
  2    day_name VARCHAR2(64);
  3  begin
  4    execute immediate ('alter session set nls_date_language=' ||'''ENGLISH''');
  5    
  6    day_name := to_char(sysdate,'day');
  7    dbms_output.put_line('"'||day_name||'"');
  8    
  9    IF day_name in ('monday', 'tuesday','wednesday','thursday','friday') THEN
 10      dbms_output.put_line('Day is OK');     
 11    ELSE
 12      dbms_output.put_line('Day is not OK!');     
 13    END IF;
 14  end;
 15  /
"tuesday  "
Day is not OK!

PL/SQL procedure successfully completed.

SQL> 

Regardless I have no influence in to_char part, day_name has some extra blanks at the end.

When this is found, fix is really easy ... add truncate (in both ways to be sure!):

SQL> set serveroutput on size 123456;
SQL> declare
  2    day_name VARCHAR2(64);
  3  begin
  4    execute immediate ('alter session set nls_date_language=' ||'''ENGLISH''');
  5    
  6    day_name := rtrim(ltrim(to_char(sysdate,'day')));
  7    dbms_output.put_line(day_name);
  8    
  9    IF day_name in ('monday', 'tuesday','wednesday','thursday','friday') THEN
 10      dbms_output.put_line('Day is OK');     
 11    ELSE
 12      dbms_output.put_line('Day is not OK!');     
 13    END IF;
 14  end;
 15  /
tuesday
Day is OK

PL/SQL procedure successfully completed.

SQL> 

The End

Be careful when work with VARCHAR2 function result which someone else create ... even this is original Oracle function.

Hope this helps someone ...

Cheers!