Query question/help

[trojnfn]

I have this following query statement :

SELECT DISTINCT
CASE WHEN activity_id='2' then activity_date ELSE '' END as activity_date

If the activity id is 2, then I want the activity date. If it is not 2, then I do not want the date, leave blank.

When I run this thru query analyzer, the dates that are not equal to 2 come out as 1900-01-01 00:00:00.000.
I then try to eliminate by using Where activity_date <> '1900-01-01 00:00:00.000' but that does not do any good.
My final cfoutput shows the date as 01/01/2009 for activity id 2 and 01/01/1900 for activity id not 2, using dateformat. How can I eliminate this date ?

The datatype is datetime, length 8, in sql server.

[Dan_Bracuk]

How about
select case when activity_id = 2 then activity_date else null end activity_date ?

[trojnfn]

I tried that and instead of displaying 1900-01-01 00:00:00.000, it displays NULL. To try and eliminate this, I use where activity_date <> NULL and get back zero response (blank page). If I do not try and eliminate, the output shows NULL in the activity date column, instead of 1900-01.....

I just want to eliminate this bad date from showing up, how can I do that ?

[Novice2002]

The result you want at the end of all these is a bit confusing, you pointed out that the first query you ran gives you "1900-01-01 00:00:00.000" which is not what you want, which led you to try and eliminate it, then you used Dan's query that removed the undesired "1900-01-01 00:00:00.000" and it rightly sticks NULL into the database when id is not 2, so what what do you expect to insert into the database when id is not 2, if it's not NULL or "1900-01-01 00:00:00.000"?

[Dan_Bracuk]

This is a cold fusion forum. If you ultimately intend to use this query in Cold fusion, you should find that your original code will work. If you want to see an empty string in query analyser, use the necessary function to change the date to a string in your original query.