Character to Date Conversion keeps failing???

Question

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!

Answer 1

0

are you sure all your candidate data is valid for conversion. Try ISDATE to check...

answered Jan 26 '11 at 07:02 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 2

0

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.

answered Jan 26 '11 at 07:04 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

(comments are locked)

Answer 3

0

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

answered Jan 26 '11 at 07:10 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Character to Date Conversion keeps failing???

Follow this question

Related Questions