database - PL/SQL Script verification -

i have date column. task create udf. basic purpose of udf check year of date. in oracle.

if year less 1753 assign year 1753 , return date.

ex:

1) select xyz_fun('1800-01-01') a_table => return 1800 - 01 -01 2) select xyz_fun('1600-01-01') a_table => return 1753 - 01 -01 3) select xyz_fun('0001-01-01') a_table => return 1753 - 01 -01 

return value should date.

i've written udf, returns warning, though no warning shown.

create or replace function someschema.change_date(date1 in date) return date ;  begin   if( extract(year date1) < 1753 )           return to_date('1753'||'-'|| to_char(date1,'mm')||'-'|| to_char(date1,'dd'),'yyyy-mm-dd');     else     return date1;     end if;  end; 

you can issue 'show errors' in sql*plus see errors.

but should not have semicolon on end of first line, should has as or is:

create or replace function someschema.change_date(date1 in date) return date begin   if( extract(year date1) < 1753 )           return to_date('1753'||'-'|| to_char(date1,'mm')||'-'|| to_char(date1,'dd'),'yyyy-mm-dd');     else       return date1;     end if; end; / 

what you're doing doesn't quite match said though; you're keeping month , day original date, seems odd. if want 1753-01-01 can use ansi date literal:

     return date '1753-01-01'; 

---

to deal leap years (as per comment), using add_months , difference in years, assuming you're happy 02-29 adjusted 02-28 (and simplifying bit per david's answer):

create or replace function change_date(date1 in date) return date begin     return greatest(date1,         add_months(date1, 12 * (1753 - extract(year date1)))); end; /  alter session set nls_date_format = 'yyyy-mm-dd'; select change_date(date '1600-02-29') dual;  change_dat ---------- 1753-02-28