PL/SQL Script verification

Question

I have a date column. Now my task is to create a udf. The basic purpose of this UDF is to check an year of a date. This is in ORACLE.

If year is less than 1753 assign year as 1753 and return date.

Ex:

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

Return value should be Date.

I've written a UDF, but it returns warning, though no warning is shown.

create or replace function someschema.change_date(date1 in date) return date
;

begin
  if( extract(year from date1) < 1753 )
    then
      return to_date('1753'||'-'|| to_char(date1,'MM')||'-'|| to_char(date1,'dd'),'yyyy-MM-dd');
    else
    return date1;
    end if;

end;

Consider simplifying your expression, perhaps: to_date('1753'||to_char(date1,'-MM-dd'),'yyyy-MM-dd') — David Aldridge
– David Aldridge, Commented May 10, 2013 at 9:29

Alex Poole · Accepted Answer · 2013-05-10 14:53:33Z

3

You can issue 'show errors' in SQL*Plus to see the errors.

But you should not have a semicolon on the end of the first line, it should has as or is:

create or replace function someschema.change_date(date1 in date) return date as
begin
  if( extract(year from date1) < 1753 )
    then
      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 what you said though; you're keeping the month and day from the original date, which seems odd. If you do actually want 1753-01-01 then you can use an ANSI date literal:

     return date '1753-01-01';

To deal with leap years (as per comment), you could do something like this using add_months and the difference in years, assuming you're happy for 02-29 to be adjusted to 02-28 (and also simplifying a bit as per David's answer):

create or replace function change_date(date1 in date) return date as
begin
    return greatest(date1,
        add_months(date1, 12 * (1753 - extract(year from date1))));
end;
/

alter session set nls_date_format = 'YYYY-MM-DD';
select change_date(date '1600-02-29') from dual;

CHANGE_DAT
----------
1753-02-28

edited May 10, 2013 at 14:53

answered May 10, 2013 at 9:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Poole Over a year ago

@Prashanth - incidentally, if you are keeping the month and day, you need to deal with leap years somehow. select change_date(date '1600-02-29') from dual will throw ORA-01839: date not valid for month specified, because 1753-02-29 is not a valid date.

Prashanth Over a year ago

Yeah.. I just faced it.. Thanks.. :)

Alex Poole Over a year ago

@Prashanth - I added a version that adjusts the year using add_months rather than just forcing it, which adjusts for leap years... losing a day in the process, but that might be a minor consideration...

David Aldridge · Accepted Answer · 2013-05-10 09:35:09Z

3

Is this a problem with passing data to early versions of SQL Server, where it doesn't store dates prior to 1753? Or a Gregorian calendar issue (which Oracle deals with OK)?

Anyway, you might like to try this in pure SQL as well as the PL/SQL method:

to_date(to_char(greatest(1753,to_number(to_char(my_date,'yyyy'))),'fm0000')||to_char(my_date,'-MM-dd'),'yyyy-mm-dd')

answered May 10, 2013 at 9:35

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

1 Comment

Kirill Leontev Over a year ago

shouldn't it be greatest?

Collectives™ on Stack Overflow

PL/SQL Script verification

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related