>>>>>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 )
>
>In SQL 2012 and up just use TRY_CAST instead of CAST()

My bad. I should have said its 2008 R2

TRY_CAST sounds useful though