MySQL CAST DateTime field AS TIME problem

Suppose I have a MySQL 5 table with a DateTime field in it. The table
has two records where the time is blank. Running:

SELECT CAST(DateTimeField AS TIME) FROM MyTable

shows two records with NULL as the resultset. Running:

SELECT * FROM MyTable
WHERE CAST(DateTimeField AS TIME)=NULL

shows no records. Why? How can I get this query to work? Thanks in
advance.

_______
Free Windows Clipboard Utility
http://www.clipboardmagic.com/

Re: MySQL CAST DateTime field AS TIME problem

Mike the Canadian wrote:
> Suppose I have a MySQL 5 table with a DateTime field in it. The table
> has two records where the time is blank. Running:
>
> SELECT CAST(DateTimeField AS TIME) FROM MyTable
>
> shows two records with NULL as the resultset. Running:
>
> SELECT * FROM MyTable
> WHERE CAST(DateTimeField AS TIME)=NULL

Three problems with this.

1. Comparing to NULL never uses the = operator. Use "IS NULL" instead.
NULL is not a value, it's a state.

2. The only case where the time portion of a DATETIME would be NULL is
if the DATETIME is itself NULL. So, no need to cast it if that's what
you're searching for.

3. If instead you're searching for the time portion = 00:00:00, then
test for that value:

WHERE CAST(DateTimeField AS TIME) = '00:00:00'

or alternatively:

WHERE TIME(DateTimeField) = '00:00:00'


Regards,
Bill K.