>>>HYi
>>>
>>>I'm taking some data from adatabse with datetime2 fields
>>>
>>>the SQL casts these as a datetime
>>>
>>>CAST(D.UNAVAILABLE_FROM_DATE AS DATETIME) AS UNAVAILABLE_FROM_DATE
>>>
>>>and I'm getting an error
>>>
>>>The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
>>>
>>>any ideas on the best way to find the offending date . (theres about 200000 records)
>>>
>>>at the moment I'm selecting the date without the cast and ordering by the date but there's quite a few of these date casts so I wondered if there was a quicker way.
>>>
>>>Thanks
>>>
>>>Nick
>>>
>>>Thanks
>>>
>>>Nick
>>
>>What is the result of this:
>>

>>SELECT * FROM D
>>WHERE YEAR(UNAVAILABLE_FROM_DATE)  < 1753
>>

>
>Or better
>

>where unavailable_from_date < '17530101'

Thanks naomi and Boris

Supplementary Question

Anyway to stop a query failing if there is an invalid date encountered (replace it with NULL maybe )