question

FLSchmidtK avatar image
FLSchmidtK 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!
sql-server-2005datetimecast-convert
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
are you sure all your candidate data is valid for conversion. Try ISDATE to check...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.