This one catches me out more often than I’d like to admit. It all starts off easy – I take a look at what day of the week it is.
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
WEDNESDAY
No problem. Now I’ll build some code logic around that query in order to run certain tasks on certain days
SQL> set serverout on
SQL> begin
2 dbms_output.put_line('Today is '||to_char(sysdate,'DAY'));
3
4 if to_char(sysdate,'DAY') = 'SUNDAY' then
5 dbms_output.put_line('I am going to execute this SUNDAY task');
6 elsif to_char(sysdate,'DAY') = 'MONDAY' then
7 dbms_output.put_line('I am going to execute this MONDAY task');
8 elsif to_char(sysdate,'DAY') = 'TUESDAY' then
9 dbms_output.put_line('I am going to execute this TUESDAY task');
10 elsif to_char(sysdate,'DAY') = 'WEDNESDAY' then
11 dbms_output.put_line('I am going to execute this WEDNESDAY task');
12 elsif to_char(sysdate,'DAY') = 'THURSDAY' then
13 dbms_output.put_line('I am going to execute this THURSDAY task');
14 elsif to_char(sysdate,'DAY') = 'FRIDAY' then
15 dbms_output.put_line('I am going to execute this FRIDAY task');
16 elsif to_char(sysdate,'DAY') = 'SATURDAY' then
17 dbms_output.put_line('I am going to execute this SATURDAY task');
18 end if;
19 end;
20 /
Today is WEDNESDAY
I am going to execute this WEDNESDAY task
PL/SQL procedure successfully completed.
Perfect! Working as expected. I roll that code into Production, and before I can even start thinking about my bonus 🤣, the phone rings
“Your code does not work”
So I give it a run.
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
THURSDAY
SQL> set serverout on
SQL> begin
2 dbms_output.put_line('Today is '||to_char(sysdate,'DAY'));
3
4 if to_char(sysdate,'DAY') = 'SUNDAY' then
5 dbms_output.put_line('I am going to execute this SUNDAY task');
6 elsif to_char(sysdate,'DAY') = 'MONDAY' then
7 dbms_output.put_line('I am going to execute this MONDAY task');
8 elsif to_char(sysdate,'DAY') = 'TUESDAY' then
9 dbms_output.put_line('I am going to execute this TUESDAY task');
10 elsif to_char(sysdate,'DAY') = 'WEDNESDAY' then
11 dbms_output.put_line('I am going to execute this WEDNESDAY task');
12 elsif to_char(sysdate,'DAY') = 'THURSDAY' then
13 dbms_output.put_line('I am going to execute this THURSDAY task');
14 elsif to_char(sysdate,'DAY') = 'FRIDAY' then
15 dbms_output.put_line('I am going to execute this FRIDAY task');
16 elsif to_char(sysdate,'DAY') = 'SATURDAY' then
17 dbms_output.put_line('I am going to execute this SATURDAY task');
18 end if;
19 end;
20 /
Today is THURSDAY
PL/SQL procedure successfully completed.
What? I didn’t run Thursday’s task even though today is Thursday.
The cause here is a simple but often overlooked one. Lets add some ‘bookends’ to my TO_CHAR function
SQL> select '~'||to_char(sysdate,'DAY')||'~' from dual;
'~'||TO_CHAR(SYSDATE,'DAY')||'~'
--------------------------------------
~THURSDAY ~
SQL> select '~'||to_char(sysdate,'DAY')||'~' from dual;
'~'||TO_CHAR(SYSDATE,'DAY')||'~'
--------------------------------------
~TUESDAY ~
SQL> select '~'||to_char(sysdate,'DAY')||'~' from dual;
'~'||TO_CHAR(SYSDATE,'DAY')||'~'
--------------------------------------
~MONDAY ~
The result of TO_CHAR(…,’DAY’) is a fixed width string. It just turns out that Wednesday is the longest of the day strings (in English). To strip off the spaces, you can use the familiar FM modifier.
SQL> select '~'||to_char(sysdate,'FMDAY')||'~' from dual;
'~'||TO_CHAR(SYSDATE,'FMDAY')||'~'
--------------------------------------
~MONDAY~
but in general, use caution with using words as inputs to code conditional logic. Don’t forget there is a whole giant soup of NLS settings out there waiting to catch you out🤣
Ho Ho Ho! Merry Christmas!
Learning is not a spectator sport 
Leave a reply to John K Cancel reply