Using CASE to convert NULL to convert NULL to a varchar with datetime and varchar field

I'm obviously novice at SQL.  I am creating a stored procedure and I am creating a tempory table and using this statement as one of the insert values.

(case when Checks.ditronicsSettledate = NULL then CONVERT(VARCHAR(11),'NA')
 else (SELECT CONVERT(VARCHAR(11),Checks.ditronicsSettleDate, 101) AS [MM/DD/YYYY])

what I am trying to do is if a Checks.dtironicsSettledate is NULL I want to store the value NA , else I want to store the date in the character field.

Chekcs.ditronicsSettledate is a datetime field. and the field I am using this statement to insert into is a varchar(11) field.

The sp compiles fine but when I run it , NULL is not replaced by NA. I tried the statement with out the CONVERT(varchar(11),NA) as just Checks.ditronicsSettledate = NULL than 'NA' but I recieved errors regarding not being able to convert and int to a varchar.

Who is Participating?
dqmqConnect With a Mentor Commented:
Agree, COALESCE or ISNULL is preferable to CASE.  Please disregard my proposed solution.
Aneesh RetnakaranDatabase AdministratorCommented:
(case when Checks.ditronicsSettledate IS NULL then 'NA'
 else (SELECT CONVERT(VARCHAR(11),Checks.ditronicsSettleDate, 101) AS [MM/DD/YYYY])

svasilakosAuthor Commented:
Of course not THEN. I must be tired. Thank you very, very much
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Definately cannot use  somevalue = NULL.   That will always test false because NULL is not equal to anything. Try

(case when Checks.ditronicsSettledate IS NULL then
    CONVERT(VARCHAR(10),Checks.ditronicsSettleDate, 101)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you don't need CASE:

COALESCE( CONVERT(VARCHAR(11),Checks.ditronicsSettleDate, 101) ,'NA') AS [MM/DD/YYYY],
Try this one
(SELECT CONVERT(VARCHAR(11), ISNULL(Checks.ditronicsSettleDate, 'NA'), 101) AS [MM/DD/YYYY])

I think this should work.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.