asked
Character to Date Conversion keeps failing???
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!
ok, after looking at the code i posted...i feel like an idiot because the case statement does the same thing in both instances. That just shows that staying up late to try harder to get the job done is not always best! The data looks like this: workdate day timein1 timeout1 timein2 timeout2 2005-07-05 00:00:00.000 Tue 6:53 12:53 1:25 5:30 What I need to do is put the workdate and times together so I can get the difference in timeworked. for example, 2005-0705 00:00:00.000 + 6:53 = 2005-07-05 00:06:53:00.000 but the only way to do this is to convert the time punches to datetime and then add the two together. There are no dates in the time punch columns and have already utilized the isdate() on the workdate colummn with no non dates. Is there another way to do this? I tried creating a package in SSIS but it takes soooooooooooo flippin long to process just 10000 rows. When I did do the 10000 row sample the error output file gave me mostly blank records in the time punches, but there were a few with numbers so not sure what the pattern is. I appreciate the immediate response!! Thanks! K
are you sure all your candidate data is valid for conversion. Try ISDATE to check...
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.
One aside, your CASE statements aren't really necessary: CASE timeout1 WHEN '' THEN CAST('' AS DATETIME) ELSE CAST(timeout1 AS DATETIME) END is just the same as CAST timeout1 AS DATETIME
