471,090 Members | 1,342 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
3 2339
Mage <ma**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Craig Thomson | last post: by
1 post views Thread by Igor Kramarsich - EDIT | last post: by
20 posts views Thread by Max Sandman | last post: by
5 posts views Thread by Nick Flandry | last post: by
2 posts views Thread by adams114 | last post: by
1 post views Thread by romiko2000 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.