Обсуждение: dealing with invalid date

          Hi,

can pgsql acceppt invalid date values? Sometimes it would be nice to
convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead
of throwing back an error message.

       Mage

Mage <mage@mage.hu> writes:

>           Hi,
>
> can pgsql acceppt invalid date values? Sometimes it would be nice to
> convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically
> instead of throwing back an error message.

If you want MySQL, you know where to find it.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> can pgsql acceppt invalid date values? Sometimes it would be nice to
> convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead
> of throwing back an error message.

I guess the question is 'accept from where?'

This isn't a database question as much as it is a data INPUT question.

Take it from one who has spent 30 years dealing with user data, you DO NOT
WANT 'bad' data in your database, you want to clean it up before it
gets into the database, and you probably don't want the database
back end making decisions about how to fix data problems, because what
it does might not be what you want.  What's the best corrected value
for the date string '13/34/2004'?  Beats me!  Sometimes the best answer is
"I don't know what you really mean here, try again."

You can certainly define a clean_date function in pl/pgsql (among other
choices) to take a string and fix whatever you want to fix before
converting it to a date.  You can also do that in perl or PHP or whatever
it is you're writing the user interface in.  Making the decision of when
and how to do that is a large part what I consider my 'value added' role
in designing a database system for a client.
--
Mike Nolan

On Sat, 2004-10-16 at 21:03, Mage wrote:
>           Hi,
>
> can pgsql acceppt invalid date values? Sometimes it would be nice to
> convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead
> of throwing back an error message.

If you're using a language with a date / time lib that does that, just
massage it through there first.

PostgreSQL is known for following the SQL spec, and more importantly for
considering your data to be important.  Carelessly munging dates to fit
them into a date field is a bad thing when it comes to your data.