Re: Validity check in to_date? - Mailing list pgsql-sql

From Iain
Subject Re: Validity check in to_date?
Date
Msg-id 007201c3b943$aeeeaa50$7201a8c0@mst1x5r347kymb
Whole thread Raw
In response to Validity check in to_date?  ("Alexander M. Pravking" <fduch@antar.bryansk.ru>)
List pgsql-sql
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@megazone.bigpanda.com>
To: "Iain" <iain@mst.co.jp>
Cc: "Karel Zak" <zakkr@zf.jcu.cz>; "Christoph Haller" <ch@rodos.fzk.de>;
"Alexander M. Pravking" <fduch@antar.bryansk.ru>; <pgsql-sql@postgresql.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



pgsql-sql by date:

Previous
From: "Devin Atencio"
Date:
Subject: Datatype Inet and Searching
Next
From: greg@turnstep.com
Date:
Subject: Re: Updating session id based on accesstimeout