SQL - Error converting date and/or time from character string

Question

I have a datetime column which I'm checking for null. All I want to do is have it say "No Next Appointment" if the column is null. However I'm getting an error:

conversion failed when converting date and/or time from character string

I'm not sure why. I tried a couple different ways to write the query but it keeps giving me the same error.

Here is my query-

SELECT 
t1.PatientID,
t1.FullName,
t1.CurrentRAF_DW,
t1.NumberOfCompletedClinicVisits,
t1.PreferredServiceLocation,
case when t1.IsVip = 1 then 'Yes' else 'No' end as [IsVip], 
case when t1.PatientTier = 'Critical' then 'Every Three Weeks' 
     when t1.PatientTier = 'Serious' then 'Every 1 Month'
     when t1.PatientTier = 'Fair' then 'Every 2 Months'
     when t1.PatientTier = 'Good' then 'Every 3 Months'
else ' '    
end as [Cadence],
t1.PatientTier,
t2.hcc_18,
t2.hcc_19,
t2.hcc_21,
t2.hcc_22,
t2.hcc_12,
t2.hcc_136,
t2.hcc_137,
t2.hcc_108,
t2.hcc_88,
t2.hcc_111,
t2.hcc_85,
t2.hcc_55,
t1.LastCompletedClinicVisit,
case when t1.NextScheduledClinicVisit is not null then t1.NextScheduledClinicVisit else 'No Next Appointment' end as [NextScheduledVisit]
FROM vw_patient_attributes t1
INNER JOIN STG_OSHODS_DW.osh_rpt.dim_member_care_measures t2
    ON t1.PatientID = t2.emr_id

The column in question here is "NextScheduledClinicVisit".

Column [NextScheduledVisit] can't be two data types. Cast t1.NextScheduledClinicVisit to a varchar so that it and your text 'No Next Appointment' are both varchar data. Something like CONVERT(VARCHAR(50), t1.NextScheduledClincVisit, 101) instead — xQbert
– xQbert, Commented Aug 30, 2017 at 20:21
case when t1.NextScheduledClinicVisit is not null then cast(t1.NextScheduledClinicVisit as varchar(40)) else 'No Next Appointment' end as [NextScheduledVisit] — Hackerman
– Hackerman, Commented Aug 30, 2017 at 20:25

Gordon Linoff · Accepted Answer · 2017-08-30 20:22:33Z

5

First, you should use coalesce(). Second, the types should match:

coalesce(convert(varchar(255), t1.NextScheduledClinicVisit ),
         'No Next Appointment' 
        ) as [NextScheduledVisit]

If NextScheduledClinicVisit is a sting, then you probably want to use an appropriate conversion specification (such as 121) or use format().

answered Aug 30, 2017 at 20:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krazy Dev Over a year ago

It is a datetime. This worked. Thanks man! I'm trying to mark this as "answered" but it says I can do that in 8 minutes haha

neuhaus · Accepted Answer · 2017-08-30 20:22:01Z

2

The column will sometimes contain a datetime field and sometimes a string.

Try casting the datetime to a varchar.

answered Aug 30, 2017 at 20:22

neuhaus

4,1741 gold badge13 silver badges29 bronze badges

Comments

John Drinane · Accepted Answer · 2017-08-30 21:22:13Z

0

I'd go about solving your issue using the following method.

Wrap the error generating column in a try_convert and review some sample records
If above successful write your case statement using a "case when [col] is null then 'formatted text 1' ..." type statement and review some sample records

Apparently Gordon's answer is working so use that, but I might look into try_Convert for future reference.

answered Aug 30, 2017 at 21:22

John Drinane

1,4863 gold badges16 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL - Error converting date and/or time from character string

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related