The reason it is failing is because your default date format is dmy. Here is some test code I ran here at home.

set dateformat mdy

select cast('2009-07-23 00:00:00' as datetime) -- works

go

set dateformat dmy

select cast('2009-07-23 00:00:00' as datetime) -- fails

go

set dateformat dmy

select cast('23-07-2009 00:00:00' as datetime) -- works

go

If you add set dateformat mdy before your first query, it will (or should) work.