Problem comparing to_char (myDate, 'DAY') result with string

Question

I tried to find what might be the problem, but I was just out of luck and did not understand the problem at all. I have the following code:

CREATE OR REPLACE FUNCTION ckeckDay(dateC in date) RETURN VARCHAR IS day VARCHAR(15); checkFriday VARCHAR(1); BEGIN checkFriday := 'N'; day := to_char(dateC, 'DAY'); IF day = 'FRIDAY' THEN checkFriday := 'Y'; END IF; RETURN day; END; / 

dateC set on Friday (he even checked it by returning day instead of day , and it returns Friday.) However, the IF never evaluates to true, even if day is really Friday. Any ideas how to get around this. thanks

Answer 1

This is because the day variable contains an empty populated value. Use the trim function to get rid of the leading and trailing spaces:

 IF trim(day) = 'FRIDAY' THEN checkFriday := 'Y'; END IF; 

And use the VARCHAR2 data type for string variables. Do not use VARCHAR .

Answer 2

If you want to be very sure of this, then you must force NLS to English and apply the FM fill mode format model to trim leading and trailing spaces.

 If To_Char(DateC,'fmDAY', 'nls_date_language=english') = 'FRIDAY' Then ...