locked
Help with case statement RRS feed

  • Question

  • Hi,

     

      I have the below line of code in SQL which i want to modify to show 'Demand' if the date field is null. I tried using Case and if statement but still gives me an error. This is my query

     

    LN.MAT_DT AS 'MATURITY DATE'

     

    I tried using

     

    case when ln.mat_dt is null then 'Demand' else ln.mat_dt end as 'Date'

     

    I get the following error is I used the above line.

     

    Msg 241, Level 16, State 1, Line 3

     

    Conversion failed when converting datetime from character string.

     

     

     

    Any help will be appreciated. Thank you

     

    Thursday, April 8, 2010 2:10 PM

Answers

  • Thank you Jason. I appreciate the help.
    • Marked as answer by YPandey Thursday, April 8, 2010 2:40 PM
    • Marked as answer by YPandey Thursday, April 8, 2010 2:40 PM
    Thursday, April 8, 2010 2:40 PM

All replies

  • Can you paste the actual Case code?
    Thursday, April 8, 2010 2:14 PM
  • Hi Ferry,

        This is what I tried to use.

    (case when ln.mat_dt is null then 'Demand' else ln.mat_dt end) as 'Date'

     I just need the above line converted to incldue a string if the condition is met.

    Thanks

    Thursday, April 8, 2010 2:16 PM
  • The conversion fails because you try to convert the field Demand to a Date. What kind of object is the field demand?

    Thursday, April 8, 2010 2:19 PM
  • I am not trying to convert the Demand field. I am trying to convert a datetime field -LN.MAT_DT - to string "Demand" if the datetime field returns empty.
    Thursday, April 8, 2010 2:21 PM
  • Yes, but the expression you use tries to convert whatever value gets returned from the case into a date. So if Ln.mat_dt is Null, it returns the string 'Demand'. Then it tries to convert that into a date, and will fail.

    What do you mean when you say that you want to convert the Date time field to a string 'Demand'.

    • Proposed as answer by RMehta Thursday, April 8, 2010 2:34 PM
    Thursday, April 8, 2010 2:29 PM
  • Hi Pandey,

    The issue is that your case statement returns different datatypes for the same column. I think you should convert the date field to string or use a random date like 12-12-9999 and then do some formatting in the report to display it as demand.

    case when ln.mat_dt is null then 'Demand' else cast(ln.mat_dt as varchar) end as 'Date'


    Cheers,
    Jason
    My Blog
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    • Proposed as answer by RMehta Thursday, April 8, 2010 2:34 PM
    Thursday, April 8, 2010 2:32 PM
  • Thank you Jason. I appreciate the help.
    • Marked as answer by YPandey Thursday, April 8, 2010 2:40 PM
    • Marked as answer by YPandey Thursday, April 8, 2010 2:40 PM
    Thursday, April 8, 2010 2:40 PM