Null values when using case statement

Posted on 2005-05-12
Last Modified: 2010-05-18
I have the following sql:

Select sale_price,
'SaleEndDate' = CASE CONVERT(VARCHAR(12),sale_price)
         WHEN null THEN null
         WHEN '' then ''
         ELSE CONVERT(VARCHAR(10), [Sale End], 120)
from mytable

However... the sql is still returning a value for the else statement even if the sale_price IS indeed null (the sale_price shows as null in the result set).

What am I missing?
Question by:rocketTendon
    LVL 28

    Accepted Solution

    Try this:

    Select sale_price,
    'SaleEndDate' = CASE WHEN CONVERT(VARCHAR(12),sale_price) IS NULL THEN NULL
                                    WHEN CONVERT(VARCHAR(12),sale_price) = '' THEN ''
                                    ELSE CONVERT(VARCHAR(10), [Sale End], 120)
    from mytable
    LVL 3

    Author Comment

    Thanks... should have tried that... didn't even think about that.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now