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
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)
more ▼

asked Jan 26 '11 at 06:57 AM in Default

FLSchmidtK gravatar image

31 3 3 3

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
Jan 26 '11 at 08:43 AM FLSchmidtK
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
are you sure all your candidate data is valid for conversion. Try ISDATE to check...
more ▼

answered Jan 26 '11 at 07:02 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jan 26 '11 at 07:04 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

One aside, your CASE statements aren't really necessary:


is just the same as

more ▼

answered Jan 26 '11 at 07:10 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 26 '11 at 06:57 AM

Seen: 965 times

Last Updated: Jan 26 '11 at 07:01 AM