Hi
I think it is not a very good idea to fill unknown parts of a date with '1's, unless you introduce a new attribute "precision" or so, where you store which part (y for year only, m for year/month etc) of your date really can be taken seriously...
Or you store year/month/day separately. When you want to use a date function, you concatenate all parts and cast it as a date. This is clean but more complicated. It depends, how often you need date-functions. Attention: concatenation with NULL results in NULL, so you need to use coalesce.
Yours,
Chris
-------- Original-Nachricht --------
Von: "Sean Davis" <sdavis2@mail.nih.gov>
An: "pg novice" <pgsql-novice@postgresql.org>
Betreff: [NOVICE] Dates with unknown month and/or day
Datum: 05/11/07 16:21
What is the best way to treat dates with unknown month and/or day? I
have some historical data that has only year or year/month, while the
newest data has year/month/day. If there is no other option than to set
unknowns to '1', that will work, but I wanted to make sure that there
was not another way (and I can't see how there could be given the way
that date functions work).
Thanks,
Sean
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq