[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Date problem
From:       Marc Palmer <marc () anyware ! co ! uk>
Date:       2000-07-24 8:57:32
[Download RAW message or body]




Hi,

Using mysql 3.23.18 (though this also occurs with previous versions) I 
get the strange behaviour with the following SQL:

CREATE TABLE test ( column1 DATE DEFAULT NULL)

INSERT INTO test SET column1=''

SELECT * FROM test

column1
0000-00-00


Right, I know that "column1" is not a legal date, but then why is MySQL 
changing it to something worse?! Surely it should fail the INSERT? 

The result of the insert is not a valid ANSI SQL date. "A Guide To The 
SQL Standard (4th ed.)" by C.J.Date clearly states on p.263:

"1. Field values within a datetime are constrained as indicated below:

YEAR  : 0001 to 9999
MONTH : 01 to 12
DAY   : 01 to 31

Now – this would be such a problem if I wasn't using Java and JDBC! It 
turns out that Sun's java.sql.Date class takes this "0000-00-00" date 
value and returns a value of "0002-31-11"!

As a result I've had to write a workaround that uses 0001-01-01 as a 
"null" marker. I know I should be inserting a NULL in this situation but 
my infrastructure doesn't allow for that right now and if I was inserting 
NULL I would never have found this bug in MySQL!

Anyone else found this?

Cheers
--
marc@anyware.co.uk



--
---------------------------------------------------------------------
Please check "http://www.mysql.com/php/manual.php" before
posting. To request this thread, e-mail mysql-thread45124@lists.mysql.com

To unsubscribe, send a message to:
    <mysql-unsubscribe-mysql=progressive-comp.com@lists.mysql.com>

If you have a broken mail client that cannot send a message to the above \
address(Microsoft Outlook), you can use http://lists.mysql.com/php/unsubscribe.php


[prev in list] [next in list] [prev in thread] [next in thread]