does NO_ZERO_ON_DATE working bad in 5.1?

[Lucio Menci]

Hi all,

I'd like to prevent that some SQL generate wrong date, and I tried to use NO_ZERO_ON_DATE, but it seems that it doesn't run well:

select @@session.sql_mode
;
Select cast(data as datetime) As NewData, data
from MyTable
WHERE right(data,2)='00'
;
Select * From
(Select cast(data as datetime) As NewData, data
from MyTable
WHERE right(data,2)='00') as a

returns:

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

NewData Data
'2006-10-00 00:00:00', '20061000'
'2009-10-00 00:00:00', '20091000'
'2010-04-00 00:00:00', '20100400'
'2013-03-00 00:00:00', '20130300'
'2026-10-00 00:00:00', '20261000'
'2029-05-00 00:00:00', '20290500'

NewData Data
'0000-00-00 00:00:00', '20061000'
'0000-00-00 00:00:00', '20091000'
'0000-00-00 00:00:00', '20100400'
'0000-00-00 00:00:00', '20130300'
'0000-00-00 00:00:00', '20261000'
'0000-00-00 00:00:00', '20290500'

Why?

No_Zero_In_Date performs to avoid the day 00 into a temporary table, but NO_ZERO_DATE didn't perform to avoid 0000-00-00.

How can I make a select that write only valid date:
Select Case When IsValidDate(NewData) Then NewData Else Null End NewData, Data From
(Select cast(data as datetime) As NewData, data
from MyTable
WHERE [...])

My SqlEngine is Version 5.1.53