Could this simple T-SQL update fail when running on multiple processors?

Question

Assuming that all values of MBR_DTH_DT evaluate to a Date data type other than the value '00000000', could the following UPDATE SQL fail when running on multiple processors if the CAST were performed before the filter by racing threads?

UPDATE  a
SET     a.[MBR_DTH_DT] = cast(a.[MBR_DTH_DT] as date)
FROM    [IPDP_MEMBER_DEMOGRAPHIC_DECBR] a
WHERE   a.[MBR_DTH_DT] <> '00000000'

I am trying to find the source of the following error

Error: 2014-01-30 04:42:47.67
   Code: 0xC002F210
   Source: Execute csp_load_ipdp_member_demographic Execute SQL Task
   Description: Executing the query "exec dbo.csp_load_ipdp_member_demographic" failed with the following error: "Conversion failed when converting date and/or time from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error

It could be another UPDATE or INSERT query, but the otehrs in question appear to have data that is proeprly typed from what I see,, so I am left onbly with the above.

Answer 1

No, it simply sounds like you have bad data in the MBR_DTH_DT column, which is VARCHAR but should be a date (once you clean out the bad data).

You can identify those rows using:

SELECT MBR_DTH_DT 
  FROM dbo.IPDP_MEMBER_DEMOGRAPHIC_DECBR
  WHERE ISDATE(MBR_DTH_DT) = 0;

Now, you may only get rows that happen to match the where clause you're using to filter (e.g. MBR_DTH_DT = '00000000').

This has nothing to do with multiple processors, race conditions, etc. It's just that SQL Server can try to perform the cast before it applies the filter.

Randy suggests adding an additional clause, but this is not enough, because the CAST can still happen before any/all filters. You usually work around this by something like this (though it makes absolutely no sense in your case, when everything is the same column):

UPDATE dbo.IPDP_MEMBER_DEMOGRAPHIC_DECBR
  SET MBR_DTH_DT = CASE 
    WHEN ISDATE(MBR_DTH_DT) = 1 THEN CAST(MBR_DTH_DT AS DATE) 
    ELSE MBR_DTH_DT END
  WHERE MBR_DTH_DT <> '00000000';

(I'm not sure why in the question you're using UPDATE alias FROM table AS alias syntax; with a single-table update, this only serves to make the syntax more convoluted.)

However, in this case, this does you absolutely no good; since the target column is a string, you're just trying to convert a string to a date and back to a string again.

The real solution: stop using strings to store dates, and stop using token strings like '00000000' to denote that a date isn't available. Either use a dimension table for your dates or just live with NULL already.

Answer 2

Not likely. Even with multiple processors, there is no guarantee the query will processed in parallel.

Why not try something like this, assuming you're using SQL Server 2012. Even if you're not, you could write a UDF to validate a date like this.

UPDATE  a
SET     a.[MBR_DTH_DT] = cast(a.[MBR_DTH_DT] as date)
FROM    [IPDP_MEMBER_DEMOGRAPHIC_DECBR] a
WHERE   a.[MBR_DTH_DT] <> '00000000' And IsDate(MBR_DTH_DT) = 1

Most likely you have bad data are are not aware of it.

Whoops, just checked. IsDate has been available since SQL 2005. So try using it.