>>>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 )