We always use to_char(date,'day') to get day name of in_date in oracle PLSQL.
In my work I want to do below task.
If day of given day is friday then don't execute procedure otherwise execute.
So I wrote below code, You can check it and expect result
I expect the output result will be
friday
It is weekend !!!, Go to Bed
Ooooops!! the output result was
So i began debugging my code and I replace below statement
I noticed that friday has empty spaces in end of word, so I used RTRIM function to clear this empty space as below
friday
It is weekend !!!, Go to Bed
So at final I want to say
Mahmoud Ahmed El-Sayed
In my work I want to do below task.
If day of given day is friday then don't execute procedure otherwise execute.
So I wrote below code, You can check it and expect result
DECLARE
LC$CURR_DAY VARCHAR2 (10);
LD$CURR_DATE DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday
BEGIN
LC$CURR_DAY := TO_CHAR (LD$CURR_DATE, 'day');
DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY);
IF LC$CURR_DAY= 'friday'
THEN
DBMS_OUTPUT.PUT_LINE ('It is weekend !!!, Go to Bed');
ELSE
DBMS_OUTPUT.PUT_LINE ('You should do your best at work');
END IF;
END;
I expect the output result will be
friday
It is weekend !!!, Go to Bed
Ooooops!! the output result was
friday
You should do your best at work
You should do your best at work
So i began debugging my code and I replace below statement
DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY);with
DBMS_OUTPUT.PUT_LINE ('*' || LC$CURR_DAY || '*');an run code again the result was as below
*friday *
You should do your best at work
You should do your best at work
I noticed that friday has empty spaces in end of word, so I used RTRIM function to clear this empty space as below
LC$CURR_DAY := RTRIM (TO_CHAR (LD$CURR_DATE, 'day'));Now The code must be like this
DECLARENow the result is (That what we expected)
LC$CURR_DAY VARCHAR2 (10);
LD$CURR_DATE DATE := TO_DATE ('17-02-2012', 'dd-mm-yyyy'); --this is friday
BEGIN
LC$CURR_DAY := RTRIM (TO_CHAR (LD$CURR_DATE, 'day'));
DBMS_OUTPUT.PUT_LINE (LC$CURR_DAY );
IF LC$CURR_DAY = 'friday'
THEN
DBMS_OUTPUT.PUT_LINE ('It is weekend !!!, Go to Bed');
ELSE
DBMS_OUTPUT.PUT_LINE ('You should do your best at work');
END IF;
END;
friday
It is weekend !!!, Go to Bed
So at final I want to say
Not always your expectation is true, So always debug your code.
Mahmoud Ahmed El-Sayed
It is really bug.
ReplyDeletethnx for sharing this issue
welcom Mahmoud, thank you for your issues
ReplyDeleteThank you for this helpful info.
ReplyDeleteThank you for this helpful info.
ReplyDeleteand if the nls_lang of the client not AMERICAN the result well be :
ReplyDeleteالجمعة
so it's much better to solve it with specifying the NLS_LANG Addition to TO_CHAR Function .
Regards
Ibrahim Bakhsh
To guarantee that to_char will return day in English, you should use below syntax
DeleteTO_CHAR (LD$CURR_DATE, 'day', 'nls_date_language=english')
I would probably use TO_DATE ('17-02-2012', 'D')
ReplyDeleteIt gives the day of the week , an int value between 1 and 7.
Always better to compare int values than strings :-)
Anyway, the extra space is an issue ...
regards,
totally true , using number in comparing is faster than strings.
DeleteBut what I want to mention here is extra spaces in string value
THANK YOU FOR THIS HELP FULL INFO.
ReplyDelete