I am trying to convert a character string (nvarchar) to datetime. I keep getting the conversion error saying it can't convert to datetime from character string. I tested the query on a select statement and it works but when I go to do the update statement it fails! Here's what I have been using: UPDATE ks_master SET timeout1_dt = CASE timeout1 WHEN '' THEN CAST('' AS DATETIME) ELSE CAST(timeout1 AS DATETIME) END , timein2_dt = CASE timein2 WHEN '' THEN CAST('' AS DATETIME) ELSE CAST(timein2 AS DATETIME) END , timeout2_dt = CASE timeout2 WHEN '' THEN CAST ('' AS DATETIME) ELSE CAST(timeout2 AS DATETIME) END Thanks!
Chances are that you have some strings that look like dates that cannot be converted, possible due to your local settings i.e. DMY vs MDY. What format do the nvarchar strings take? Can you post some examples. It is possible that the select runs without error, as that is simply returning the data, whereas the update has to comply to the datatypes that are being updated.