>>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().
>
>Otherwise use
>

>case when DateCol < '17530101' then NULL else cast(dateCol as DATETIME) end

Thanks Naomi