Post reply

Strange CASE WHEN case

  • January 18, 2018 at 12:09 am

    #330902

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
        Apr 2 2010 15:48:46
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    SQL_Latin1_General_CP1_CI_AS


    FIRSTNAME               DOB
    FARHAN                       1900-01-01 00:00:00.000
    DAWOOD                     2004-04-22 00:00:00.000

    1)
    SELECT FIRSTNAME, CASE WHEN CONVERT(DATE, [DOB]) = CONVERT(DATE, '1900-01-01') THEN '' ELSE CONVERT(date, [DOB]) END  AS 'DOB'

    FIRSTNAME               DOB
    FARHAN                       1900-01-01
    DAWOOD                     2004-04-22

    2)
    SELECT FIRSTNAME, CASE WHEN CONVERT(DATE, [DOB]) = CONVERT(DATE, '1900-01-01') THEN '' ELSE CONVERT (VARCHAR, CONVERT(date, [DOB])) END  AS 'DOB'

    FIRSTNAME                 DOB
    FARHAN          
    DAWOOD                     2004-04-22

    Could someone please, did the first query process the ELSE part? Why this difference?

  • January 18, 2018 at 1:02 am

    #1976276

    Hi

    No, the first query did not process the else part. It has to do with the data type precedence. In the first query you are comparing date's and returning an empty string for true or a date value for false. Seeing that data has a higher precedence the empty string gets converted to date and thus resulting in 1900-01-01.

    In your second query you are returning string values that is why you get the empty string value back as you expected.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply