why a convert from varchar do date fails on a different row everytime it runs?
The reason the error occurs after varying numbers of rows are processed is because the conversion isn't done in a deterministic order.  It depends on the execution plan.  

And especially if the query is parallel (given the number of rows, I expect that it is), rows will be distributed differently across threads on each run and might hit the "Compute Scalar" in a different order each time.

Other reasons are that different indexes might be used (resulting in the rows being ordered differently), although that seems less likely given the static nature of the query.



The reason the formatting parameter doesn't work when you do this:

select convert(date,The_ugly_field,103) from Ugly_Table


...is because the 103 style you're trying to use is for converting a date or datetime to a character data type.  Not a varchar to a date (which is what that CONVERT statement does).  In this case, SQL Server is just displaying the date in the format that is the default for the instance.



You'll need to manually track down the "bad" data that can't be converted:


optional: add a MAXDOP 1 hint to the query to make the order of the data returned slightly more deterministic
look at that last rows that were successfully returned by the query
perform your SELECT without the convert and manually inspect the values in that area
Correct or remove the bad data if possible
Add a CASE statement to the SELECT to avoid trying to convert bad values, like this:


select  CASE WHEN LEN(The_ugly_field) &lt; 8 THEN '01/01/1900' ELSE convert(varchar(10), cast(The_ugly_field as date),103)  END
From Ugly_Table
order by The_ugly_field


I would suggest TRY_CONVERT, but that is only available on SQL Server 2012 and higher.