why a convert from varchar do date fails on a different row everytime it runs?

Question

I have a database with a field like this (Dont know why it's varchar):

19950105

i'm trying to convert it to DATE as DDMMYYYY.

I could do this with:

convert(varchar(10), cast(The_ugly_field as date),103) 

Then my date is:

05/01/1995

1) Why I can't convert it directly to date? if I use:

select convert(date,The_ugly_field,103) from Ugly_Table

it returns 1900-01-01 and it doesnt respect the 103. any number there returns the same format YYYY-MM-DD.

2)Now the real question. when I run

select  convert(varchar(10), cast(The_ugly_field as date),103)  
From Ugly_Table
order by The_ugly_field

after some time it returns this error:

Msg 241, Level 16, State 1, Line 29 Conversion failed when converting date and/or time from character string.

but the funny part is, it doesnt looks like the errors appears in the same row. If I run this code like 4 times, every time it returns the error, it says a different row. I mean, I execute it, then in the "results" tab I can see that it returned 1.234.555 rows. If I run it again, after the error I return to the "result" table and now the query ran until 1.556.345.

and please. how the hell can I convert this 20140101 in a elegant way to 2014/31/12? Running convert(date,E1_EMISSAO,103) doesn't seems to respect the 103 and it returns 2017-02-24 and not 24/02/2017.

tons of questions and answers but it's all about a simple convert().

Answer 1

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

Answer 2

Try this to move the year. it worked for me .

select cast(substring ( ugly_column_date, 5,8) + substring ( ugly_column_date, 1,4) as datetime) from ugly_table