question

Sue 1 avatar image
Sue 1 asked

Conversion failed when converting date and/or time from character string.

I am receiving the following error when I am trying to convert a datetime - see both codes I've tried below. I know I have bad data in my table - I need a way to get around the bad data. Thank you for your assistance. > error Msg: Conversion failed when > converting date and/or time from > character string. -- CASE -- WHEN -- IsDate(r.ReferralDonorDeathDate) is not null -- THEN -- Convert (DateTime,(Convert(VarChar (20), Isdate (r.ReferralDonorDeathDate), 101) -- + ' ' + (NullIf(Substring(ReferralDonorDeathTime,1,5), ' ')))) --END AS ReferralDonorDeathDatetime, DateDiff (mi, c.CallDateTime,Convert(DateTime,(Convert(VarChar (20), isdate (r.ReferralDonorDeathDate), 101) + ' ' + (NullIf(Substring(ReferralDonorDeathTime,1,5),' '))))) AS ReferralDonorDeathDateTime
sql-server-2008t-sqldata-conversion
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.

@Sue Please check my comments to Fatherjacks's answer. There are some other errors in the script, but they all can be addressed if you could tell more about what do you actually want to achieve. Thanks
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
`IsDate` will return 1 if the expression can be converted, and 0 if not - don't test for `null`
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.

Fatherjack avatar image
Fatherjack answered
so, expanding on Kevs answer, you need to adjust your code a little. This should be close to whats required: CASE WHEN IsDate(r.ReferralDonorDeathDate) = 1 THEN Convert (DateTime,(Convert(VarChar (20), Isdate (r.ReferralDonorDeathDate), 101) + ' ' + (NullIf(Substring(ReferralDonorDeathTime,1,5), ' ')))) else -- use a generic date such as '19000101' or NULL if the column allows it here END AS ReferralDonorDeathDatetime
3 comments
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.

+1 for not being as lazy as me.
0 Likes 0 ·
@Fatherjack This is an oversight, isn't it? The expression cannot possibly make any sense. The script is trying to convert to datetime something which is requested to be converted to varchar(20) from datetime but that is passed as **bit** (the second argument of the inner convert is Isdate(r.ReferralDonorDeathDate) instead of r.ReferralDonorDeathDate :(
0 Likes 0 ·
Additionally, I believe that nullif is used when isnull was intended. nullif does not make sense in the query in question, because if ReferralDonorDeathTime is blank or any number of spaces then **nullif('', ' ')** will return null because '' is equal to ' ' due to ANSI compliant compare algorithm, which in turn makes the whole statement evaluated to null because concat null yields null. I think that the question needs to be restated somewhat to clarify what needs to be done
0 Likes 0 ·

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.