Help with case statement

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

 

Answer 1

Can you paste the actual Case code?

Answer 2

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

Answer 3

The conversion fails because you try to convert the field Demand to a Date. What kind of object is the field demand?

Answer 4

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.

Answer 5

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'.

Answer 6

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! :)

Answer 7

Thank you Jason. I appreciate the help.