I have a datetime column called mydate. In my where clause (in SSMS) I have:

WHERE mydate <> 2029:12:31 00.00.00.000

As specified above I get error:

Incorrect syntax near '00'.

If I change it to:

WHERE mydate <> '2029:12:31 00.00.00.000'

I get:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

which seems bizarre as all the examples I see have ‘ quotes in them.

The date is copied straight from the column data.

Also:

WHERE mydate <> 2029:12:31

seems to be syntactically correct but selects rows containing 2029:12:31 00.00.00.000

So how should I secify date and time to avoid the dates with 2029:12:31 00.00.00.00?

This is a historical DB so I don’t have much hope in changing it’s properties. The date above is not a real date, just a sentinel sort of thing. I’d love to avoid using CONVERT or CAST etc.