Selecting MySQL datetime columns which are 0000-00-00 00:00:00

Question

I'm looking for a simple way to select datetime columns which are 000-00-00 00:00:00 (basically the default unset value).

The following query seems to be working:

SELECT * FROM myTable WHERE datetimeCol < 1

But will this reliably work all the time and across different mysql versions?

Answer 1

I would check for "zero" dates like this:

SELECT * FROM myTable WHERE datetimeCol = CONVERT(0,DATETIME)

I would prefer the equality predicate over an inequality predicate; it seems to convey my intentions more clearly. And if there are more predicates in the statement, and if the datetimeCol is a leading column in an index, it may help the optimizer make use of a multi-column index.

Answer 2

Try this:

SELECT * FROM myTable WHERE datetimeCol = '0000-00-00 00:00:00'

Might seem obvious in hindsight ;)