Ask yourself, what does a date with the value of "NULL" mean? Will it be equal, unequal, earlier or later than any other date? Can you do date arithmetic on it (what is "NULL" plus three days?)?

That's complete nonsense. There are plenty of good reasons to use NULL. For example, a NULL expiry date could mean there isn't an expiry date.

This is not nonsense.

SQL's use of "NULL" is inherently flawed as the relational model does not work with "NULL" values as implemented by SQL. Codd himself (in "The Relational Model for Database Management: Version 2) suggested that the SQL implementation of NULL was flawed and should be replaced by two distinct NULL-type markers. The markers he proposed were to stand for "Missing but Applicable" and "Missing but Inapplicable", known as A-values and I-values, respectively. Codd's recommendation, if accepted, would have required the implementation of a four-valued logic in SQL. None of the common RDBMs in use do this and neither does any SQL standard.

Of course, the SQL standard is a flawed implementation of the relational model in any case, but as it is "the best" (meaning the only one we have), we will have to live with it.

To give an example of how flawed the use of "NULL" is, consider the following:

1. A database table T with two columns: ID-number and Amount.
2. The following query: SELECT ID-number, Amount FROM T WHERE Amount = 10 OR NOT(Amount = 10)
3. This query should return ALL rows, since either the amount is equal to 10 or it is not. It does not matter whether the amount is actually unknown: it can only be "10" or "not 10". Yet this query will not return a row where "Amount = NULL".
Although the answer given by the SQL query is correct according to the truth tables of ternary ("three valued") logic, it is plainly wrong in the real world and one of the tenets of the relational database model is that you must be able to construct predicates on basis of the relation which are true in the real world. This is the so-called "Closed World Assumption": Everything stated by the database, either explicitly or implicitly, is true; everything else is false. The use of NULL as implemented by SQL breaks this assumption.

I agree that "NULL" comes in handy in using as a placeholder for an unknown value, but in many cases the use of "NULL" indicates that we have not been thinking the design of our database through in all its aspects and that something should be changed there.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Ask yourself, what does a date with the value of "NULL" mean?

Like undef, it either means that there isn't a date or that the date is unknown. This is, unfortunately, ambiguous, but can't really be resolved without adding a second "KNOWN BUT EMPTY" (non-)value, leaving NULL to specifically mean "unknown", since dates don't have any real equivalent to zero (for numbers) or empty strings (for text).

Will it be equal, unequal, earlier or later than any other date?

No, it will not be any of these, as you cannot know whether an unknown date is equal, unequal, earlier, or later than any other date.

Can you do date arithmetic on it (what is "NULL" plus three days?)?

No, you cannot calculate "an unknown date plus three days". The result will still be unknown.

Attempting to determine whether a known-nonexistent date is before or after a given date, or the date three days after a known-nonexistent date, is similarly nonsensical. e.g., You can't say whether I was released from prison before or after my 25th birthday or what the date was three days after I met Richard Nixon because I've neither been to prison nor seen Nixon.

If I recall correctly, SQL's semantics for handling NULL are consistent with my interpretation - any logical comparison to NULL will return false¹ and NULL + 3 = NULL.

¹ Tri-state logic would allow comparisons to more properly return "unknown", but, in boolean logic, "true" and "false" are all we've got to work with and asserting that unknown results are implicitly true would be worse than considering them to be false.

All your comments just strengthen the position that the use of "NULL" in SQL is flawed, since it has multiple meanings and there is no way to discern which of its meanings is to be considered.

If there is ambiguity in your database, how can you ever trust its results?

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

use DBI qw(:sql_types);    # make sure DBI gives you access to sql_typ
+es strings

$Array[0] = undef;
$sth = $dbh->prepare("INSERT into MYTABLE (mydate) VALUES(?)");
$sth->bind_param( 1, $Array[0], SQL_DATETIME );   # forces the data ty
+pe
$sth->execute;   # no args needed here now.
[download]

HTH,
SSF

That doesn't seem likely to me, as OP states that ->execute(undef), on the same prepared statement, does work for him.

On the other hand, though, I don't have any better theory...

fws, does the ->execute($Array[0]) version die with just "ODBC error" or does the error message contain any additional information?

Please tell us what error you get?