Problem with dates before 1900
Ben COct 29 2012 — edited Oct 30 2012I have a short Function to check whether a date is valid or not. I pass in one parameter of DATE datatype. I want to check if the Input parameter is prior to 1/1/1900.
I seem to be getting a problem due to our Database NLS Date format of 'DD-MON-RR'. If I pass the parameter as '1/1/1877', Oracle read is as '01-JAN-77' and then seems to evaluate it as 1/1/1977. I am on 10g database. The DBMS Outpu will display '01/01/1977'.
CREATE OR REPLACE function nw_date_validation(p_date IN DATE)
return boolean as valid_date_flag boolean;
-- Function to check whether a date field in eCATS batch is valid
Begin
dbms_output.put_line(to_char(v_date,'MM/DD/YYYY'));
IF p_date is null then
valid_date_flag := FALSE;
Elsif p_date < to_date('01/01/1900','MM/DD/YYYY') then
valid_date_flag := FALSE;
Else
valid_date_flag := TRUE;
End if;
return valid_date_flag;
End;
/