Compare dates in Oracle Forms:-

Compare dates in Oracle Forms:-
Forms will automatically adjust the date format depending on your max length or your format mask. Only thing is, not sure how different NLS settings will affect your individual needs.

The best way to copmare two dates is to convert them into numbers and compare.For eg.

select Emp_code,Emp_name from employee
where to_number(to_char(join_date,'yyyymmdd'))=to_number(to_char(sysdate,'yyyymmdd'))

Sometimes we store the DAY in our database column like Absent_day='MONDAY' and the oracle give different results depending on the nls setting.For eg.
Sometimes ,

select * from employee where absent_day=to_char(sysdate,'DAY') will not work as the nls lang settings are different or other than english.
so in such situations we have to write:-

select * from employee where absent_day=(LTRIM(RTRIM(TO_CHAR(sysdate ,'DAY','NLS_DATE_LANGUAGE=ENGLISH'))

One more comparision is to compare an parameter date with two existing date columns in a table:-
select* from employee_date
where to_number(to_char(:p_e_date,'yyyymmdd'))
between to_number(to_char(employee_date_from ,'yyyymmdd'))
and  to_number(to_char(employee_date_to,'yyyymmdd'));

I hope all the date related comparision must be solved .