May 27, 2016 at 10:09 am
Hi,
I have a column
[RequestTimeStamp] [datetime] NULL,
I have to build a case statement
If requestId = 3 then 'N/A' else RequestTimeStamp end as [Actual End Date]
I'm getting an error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Need suggestions with example syntax to try. I have looked at convert and cast but not working unless I have the syntax incorrect. So I'm reaching out for suggestions.
Thanks
Bev
May 27, 2016 at 10:20 am
Yes, return a NULL and let the presentation layer deal with displaying 'N/A' when the field is NULL. It is best to leave this type of formatting to the presentation layer whenever possible.
You're running into problems, because the expressions returned in a CASE expression have to be compatible, and the expressions will be converted to the type with the highest precedence. You have a string expression and a datetime expression. Since datetime has a higher precedence, it tries to convert the string expression to datetime, but 'N/A' is not a valid datetime. If you absolutely have to you can convert your datetime to a string, so that the datatypes of the two expressions match and there are no implicit conversions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 27, 2016 at 10:44 am
I agree. NULL and let the UI do the work on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 27, 2016 at 11:55 am
YOU GUYS ROCK!!!!!!
It works!
May 27, 2016 at 2:14 pm
Hi
Just one more question. How do I change NULL to N/A. The recipient of the report wants to see N/A where the NULLs are appearing
May 27, 2016 at 7:00 pm
N/A is a character value, you cant mix types within same column, so you must CONVERT() the date to character.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply