Choosing MySQL datetime columns that are 00:00:00 0000-00-00

Question

I am looking for an easy way to select datetime columns that are 000-00-00 00:00:00 (basically the default value is not set).

The following query seems to work:

SELECT * FROM myTable WHERE datetimeCol < 1 

But will it work reliably all the time and in different versions of mysql?

Answer 1

I would look at zero dates as follows:

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

I would prefer the predicate of equality over the predicate of inequality; this seems to more clearly reflect my intentions. And if there are more predicates in the expression, and if datetimeCol is the leading column in the index, this can help the optimizer use the index with multiple columns.

Answer 2

Try the following:

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

It may seem like an obvious hindsight;)

Answer 3

How about comparing with 0 value:

 datetimeCol = 0 

Answer 4

Try the following:

SELECT * FROM myTable WHERE UNIX_TIMESTAMP(datetimeCol) = 0