By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,542 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,542 IT Pros & Developers. It's quick & easy.

dealing with invalid date

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
> 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

P: n/a
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.