none
Case statement for date

    Question

  • Hi I have a result set

    Date
    2007-02-02 00:00:00.000
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    2010-07-16 00:00:00.000
    NULL
    NULL
    NULL
    NULL
    2011-01-14 00:00:00.000

    I need to write case statement where the field is null I need to get a blank in the result set.

    I have tried

    Case when date is null then '' else date end


    when I tried this I am getting 1900-01-01 00:00:00.000 in the place of nulls
    Tuesday, February 05, 2013 1:27 PM

Answers

  • There is no "blank" or empty date value, so you will have to convert the date value to string. Remember that the data type of a CASE expression is the one with higher precedence among all data types in the WHEN / ELSE parts.

    select cast('' as date) as dt;
    GO

    Here is the expression:

    CASE WHEN [date] IS NULL THEN '' ELSE CONVERT(varchar(35), [date], 126) END as dt


    AMB

    Some guidelines for posting questions...

    • Marked as answer by RVD's Tuesday, February 05, 2013 1:43 PM
    Tuesday, February 05, 2013 1:34 PM

All replies

  • There is no "blank" or empty date value, so you will have to convert the date value to string. Remember that the data type of a CASE expression is the one with higher precedence among all data types in the WHEN / ELSE parts.

    select cast('' as date) as dt;
    GO

    Here is the expression:

    CASE WHEN [date] IS NULL THEN '' ELSE CONVERT(varchar(35), [date], 126) END as dt


    AMB

    Some guidelines for posting questions...

    • Marked as answer by RVD's Tuesday, February 05, 2013 1:43 PM
    Tuesday, February 05, 2013 1:34 PM
  • I have just posted a sample piece of my actual data i have got 14000 odd records.
    Tuesday, February 05, 2013 1:35 PM
  • SELECT CAST(dt AS DATEtime) dt INTO vt FROM
    (
    SELECT '2007-02-02 00:00:00.000' dt UNION
    SELECT NULL UNION
    SELECT NULL UNION
    SELECT '2010-07-16 00:00:00.000' UNION
    SELECT NULL UNION
    SELECT NULL UNION
    SELECT '2011-01-14 00:00:00.000'
    )rs



    SELECT ISNULL(CONVERT(VARCHAR(40),dt,121),'') FROM vt

     SELECT CASE WHEN dt IS NOT NULL THEN CONVERT(VARCHAR(40),dt,121) ELSE '' END FROM vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Tuesday, February 05, 2013 1:39 PM
  • Try

    create table abc(id int identity,def datetime)
    
    insert into abc values(GETDATE())
    insert into abc values(GETDATE()-1)
    insert into abc values(NULL)
    
    
    select id,case when cast(def as varchar(40)) is null
    			 then '' 
    			 else cast(def as varchar(40)) end
    			 From abc


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, February 05, 2013 1:40 PM