CASE WHEN Date if IS NULL

I'm trying to do a crosstab query based on a date. For some reason I can't return the column to display a NULL or blank in the field instead of 1900-01-01 00:00:00.000. I'm using a CASE WHEN to display data.

SELECT     CRM.EventId, CRM.EventName, CRM.City, CRM.State, CRM.ContactName, CRM.ContactNumber, CRM.Email, CRM.DomainName, CRM.RunningInTheUSA, 
					  (CASE WHEN ec.CategoryID = 1 THEN CRM.EventDate WHEN 1 = 1 THEN '' ELSE CAST(NULLIF(CRM.EventDate, '') AS date) END) AS A, 
                      (CASE WHEN ec.CategoryID = 2 THEN CRM.EventDate WHEN 1 = 1 THEN '' ELSE CAST(NULLIF(CRM.EventDate, '') As Date) END) AS B
FROM         CRMEventDetail AS CRM LEFT OUTER JOIN
                      aspnet_Users AS u ON CRM.AccountManager = u.UserId LEFT OUTER JOIN
                      EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE     (CRM.EventDate >= { fn NOW() })
ORDER BY A DESC

Open in new window

Southern_GentlemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
CASE
   WHEN ec.CategoryID = 1 THEN CRM.EventDate
   WHEN 1 = 1 THEN ''
   ELSE CAST(NULLIF(CRM.EventDate, '') AS date)

breaking it down...

CASE
   WHEN {some condition} THEN {date value}
   WHEN {some condition} THEN {'', which is a character value}
   ELSE {date value OR ''}

Can't do that in a CASE block.  All THEN values have to be the same data type, and SQL is going to interpret it from the first WHEN..THEN value.
Should be able to display NULLs in a date column.

Here's a similar question    Here's an article on CASE Solutions
Jim HornMicrosoft SQL Server Data DudeCommented:
date-null-empty-string.jpgEmpty string '' evaluates in a date column as the numeric zero, which means 1900-01-01.
NULL values evaluate as NULL.
Southern_GentlemanAuthor Commented:
The way my query is designed is that I wanted it sort of as a crosstab query. So all my dates in my data table do have a date value and there are no Null or '' values in my data table date column.
My final view that i'm looking for in column A and B should have a null if CASE THEN doesn't match the criteria, which I keep coming up with the 1900-01-01 value instead. I keep trying to convert it to a string value but that doesn't seem to work.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
before you doing the conversion for A & B, is that make sense if you verify what's the value of CRM.EventDate which correspond to ec.CategoryID ?

for example, can you tell us what you get when you have:
SELECT 
ec.CategoryID, CRM.EventDate, 
 (CASE WHEN ec.CategoryID = 1 THEN CRM.EventDate WHEN 1 = 1 THEN '' ELSE CAST(NULLIF(CRM.EventDate, '') AS date) END) AS A, 
 (CASE WHEN ec.CategoryID = 2 THEN CRM.EventDate WHEN 1 = 1 THEN '' ELSE CAST(NULLIF(CRM.EventDate, '') As Date) END) AS B
FROM CRMEventDetail AS CRM
LEFT OUTER JOIN aspnet_Users AS u ON CRM.AccountManager = u.UserId
LEFT OUTER JOIN EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE (CRM.EventDate >= { fn NOW() })
ORDER BY A DESC

Open in new window

? I guess we may need to change the case when conditions
Southern_GentlemanAuthor Commented:
Yes so when the category ID = 1 in my first column it will display a date in column header A, when the category ID = 2 then it will display dates in column header B. etc etc.

The data seems to be flowing correctly but I would like it to display '' instead of the 1900-01-01.

Blank values needed for 1900-01-01
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:
SELECT 
ec.CategoryID, CRM.EventDate, 
CASE WHEN ec.CategoryID = 1 THEN CRM.EventDate ELSE NULL END AS A, 
CASE WHEN ec.CategoryID = 2 THEN CRM.EventDate ELSE NULL END AS B
FROM CRMEventDetail AS CRM
LEFT OUTER JOIN aspnet_Users AS u ON CRM.AccountManager = u.UserId
LEFT OUTER JOIN EventCategories AS ec ON CRM.EventCategory = ec.EventCategory
WHERE (CRM.EventDate >= { fn NOW() })
ORDER BY A DESC

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Southern_GentlemanAuthor Commented:
Thanks I think I was over thinking it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.