Hope you all know about SQL functions 'to_char' , 'trim' and 'length'
'to_char' used to convert a date or number type to a character type
'trim' mostly used to delete white spaces in a given string.(we can also trim character from a string . check the SQL oracle user guide)
'length' is used to get the length(no. of characters) of the given string
Please check the following queries to understand well about this
Assume today is MONDAY
select to_char(sysdate,'DAY') from dual;
will return MONDAY. we all know that
select length(to_char(sysdate,'DAY')) from dual;
should return 6. but it will return 9. the reason behind this is to_char(sysdate,'DAY') always return a string with 9 characters. so the MONDAY will have three white spaces at the end.
why its returning always the length of 9?
the answer is the maximum character a DAY can hold is 9(WEDNESDAY). so it always pad white spaces for the remaining length
to get the correct length use the following query
select length(trim(to_char(sysdate,'DAY'))) from dual;
Hope you can use these query in day validation.
Assume today is MONDAY
The following query will return 'Today is not Monday'.
set serveroutput on;
begin
if to_char(sysdate,'DAY')='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/
To get the correct result ('Today is Monday') use the following query
set serveroutput on;
begin
if trim(to_char(sysdate,'DAY'))='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/