User entry of dates

From: John Higley <johnhi_at_biker.bv.tek.com>
Date: 1997/06/04
Message-ID: <3395C12A.167EB0E7_at_biker.bv.tek.nospam.com>#1/1

The recent articles about DATE validation have reminded me of a function that
I use. I picked up the basics for it from the net, but have modified it. I
have included it below.

But, it does have some problems:

In Forms 4 and Forms 4.5, the format RRRR is indeed a bad format. You do not
get what you expect. The format RR works fine, but you cannot see your results. The format YYYY allows the user to enter a 4-digit year, but defaults the century to 00 if the user only enters 2 digits. The main problem with YYYY is that it does not allow the user to enter 00 (as in 1-Jan-00)

My function takes whatever year the user entered, determines if it was 2 or
4 digit (a century of 00 indicated 2-digit). If the century is 00 it will
replace the year with a date that is no more than 30 years in the future or
70 years in the past.

Example: today is 4-Jun-1997. If the user enters 31-Dec-27 the date will be
converted to 31-Dec-2027 (only the year is included in the calculation)

If the user enters 31-Jan-28 the date will be converted to 31-Jan-1928

If the user enters a 4-digit year (or a 3-digit year), the date will be left
alone.

By displaying the 4-digit year, the user always knows what was entered.

As I mentioned, the user must enter the year 2000, because the YYYY format
does not allow 0 (ORA-01841: (full) year must be between -4713 and +4713)

Anyway, I hope that this proves useful.

                                        John Higley
                                        Tektronix, Inc.
                                        john.m.higley_at_tek.com

This article is my own opinion and may or may not be shared by my employer.

select
to_char(dates.fixdate(to_date('31-jan-28','dd-mon-yyyy')),'dd-Mon-yyyy') from dual;

---

create or replace package dates as
  function fixdate ( date_i date ) return date;   pragma restrict_references(fixdate, WNDS, WNPS); end;
/

create or replace package body dates as

function fixdate ( date_i date ) return date is

  v_yr2         varchar2(2) := substr(to_char(date_i,'YY'),1,2);
  v_cutoff      number(4) := to_number(to_char(sysdate,'YYYY'))-70;
  v_century     varchar2(2) := substr(to_char(date_i,'YYYY'),1,2);

begin
  if v_century = '00' then
    if v_yr2 > substr(to_char(v_cutoff),3,2) then       v_century := substr(to_char(v_cutoff),1,2);     else
      v_century := substr(to_char(v_cutoff + 100),1,2);     end if;
  end if;

  return to_date(v_century|| to_char(date_i,'YYMMDD') ,'YYYYMMDD'); end;

end;
/

grant execute on dates to public; Received on Wed Jun 04 1997 - 00:00:00 CEST