HOW-TO do incomplete dates: year, optional month, optional day?

is there a way, save lots of manual manipulation and
hand-waving, to implement PARTIAL DATES? (this may be exactly
the job for creating a new data type, but hopefully someone's
already invented this wheel...?)

"doug has worked at pinnacle since 1991".

not 1-jan-1991, not 31-dec-1991, but just plain-ol' 1991.

"beulah started here back in november of 1998."

not 1-november, not 30-november. just november, of 1999.

"my first day at acme corp was the 17th of may, 2001."

here, in that same field, we need year, month AND day.

at the moment i'm considering views and rules (with plpgsql
functions to do the re-assembly). here's a seat-of-the-pants
recreation (no syntax checking) to show my gyrations:

CREATE TABLE partial_dates(
-- yada yada
partial_year INTEGER, -- zero/null or 1492, 2001...
partial_month SMALLINT,-- zero/null, or 1-12
partial_day SMALLINT -- zero/null, or 1-31
-- yada yada
);

CREATE FUNCTION
partial_date(integer,smallint,smallint) -- y,m,d
RETURNS TEXT as '
DECLARE
yr ALIAS FOR $1;
mo ALIAS FOR $2;
dy ALIAS FOR $3;
month TEXT;
BEGIN
IF yr < 1900 THEN
RETURN ''UNKNOWN'';
END IF;
IF mo < 1 THEN
RETURN yr || text;
END IF;
SELECT INTO month abbr_en FROM month_table WHERE num = mo;
IF dy < 1 THEN
RETURN yr::text || ''-'' || month;
END IF;
RETURN yr::text || ''-'' || month || ''-'' || dy::text;
END;
' language 'plpgsql';

CREATE VIEW dates AS
SELECT
-- yada yada
partial_date(partial_year, partial_month, partial_day)
-- yada yada
FROM
partial_dates;

is there a better way?

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !