• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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.

1 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
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)
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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.

Agree, COALESCE or ISNULL is preferable to CASE.  Please disregard my proposed solution.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now