Re: Validity check in to_date?

Sweeet!

This is what I ended up with:

create domain testdate char(10) check (VALUE::text::date = VALUE);

(it wasn't possible to insert a NULL date with (VALUE::text::date IS NOT
NULL); )

I'm pretty happy with this as I didn't even have to use
to_char(VALUE::text::date , 'YYYY-MM-DD') for the comparison, since the
date format I am using matches the "datestyle" setting.

It seems to accept any valid date, as well as NULL, while rejecting inputs
such as:

insert into test1 values ('2002-03-32');
insert into test1 values ('200-03-22');
insert into test1 values ('2002- 3-22');
insert into test1 values ('2002-03-2');
insert into test1 values ('2002-03- 2');
insert into test1 values ('2002-3-2');
insert into test1 values ('2002-14-02');
insert into test1 values ('');

insert into test1 values ('2002/03/22');
insert into test1 values ('2002/03/32');
insert into test1 values ('200/03/22');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2');
insert into test1 values ('2002/3/2');
insert into test1 values ('2002/14/02');

It's no silk purse, but it's short and sweet and I'm satisfied.
Thanks guys.
Rregards
Iain

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: "Karel Zak" <zakkr(at)zf(dot)jcu(dot)cz>; "Christoph Haller" <ch(at)rodos(dot)fzk(dot)de>;
"Alexander M. Pravking" <fduch(at)antar(dot)bryansk(dot)ru>; <pgsql-sql(at)postgresql(dot)org>
Sent: Wednesday, December 03, 2003 1:15 AM
Subject: Re: [SQL] Validity check in to_date?

> On Tue, 2 Dec 2003, Iain wrote:
>
> > T've been following this thread with interest because I have a related
> > problem. Basically we are storing dates in CHAR fields with al the
> > associated problems. I'd like to do it, but changing everything to date
> > fields isn't practical for now, so as a stopgap solution, I want to
provide
> > some validation at the database level.
> >
> > I tried:
> >
> > create domain ymdtest2 as char(10) constraint valid_date check
> > (VALUE::DATE);
> >
> > But it gives this error:
> >
> > ERROR: cannot cast type character to date
> >
> > I also tried:
> >
> > create domain test char(10) check (CAST(VALUE AS DATE));
>
> I'd try CAST(CAST(VALUE AS TEXT) AS DATE)
>
> There's a text->date conversion, but not one from character(n).
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org