MySQL Forums
Forum List  »  Microsoft SQL Server

Verify invalid dates/datetimes
Posted by: Daniel Ciulinaru
Date: December 22, 2008 05:52PM

Hi,

Can someone tell me if there's any reliable (and I emphasize reliable) way to check values for invalid dates upon select, NOT insert?
I turned on session strict mode - it doesn't help. Here I have a row, possible a lot more following, of invalid dates (this is in session strict mode):

mysql> select id, date_created, date(date_created), isdate(date_created), cast(date_created as datetime) + interval 0 day as cast1, cast('2008-12-00 10:00:00' as datetime) + interval 0 day as cast2 from event_log_200805_5_a where id = 869993980\G
*************************** 1. row ***************************
id: 869993980
date_created: 2000-01-00 00:00:00
date(date_created): 1900-01-00
isdate(date_created): 1
cast1: NULL
cast2: NULL
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '1900-01-00 00:00:00' |
| Warning | 1292 | Truncated incorrect datetime value: '2008-12-00 10:00:00' |
+---------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

isdate() is a custom function that supposedly checks for date reliability. However, the way it is written relies on the mysql's date () which, surprise, surprise, also accepts invalid entries, so it doesn't achieve its goal.

As it turns out from the above select, one way would be to dome math operation on the specified date but I'm afraid it is not going to be the only error possibility.

Thanks in advance for the help.

Options: ReplyQuote


Subject
Written By
Posted
Verify invalid dates/datetimes
December 22, 2008 05:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.