Skip to main content
T
trojnfn
Inspiring
January 9, 2009
Question

Query question/help

  • January 9, 2009
  • 3 replies
  • 373 views
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.
    This topic has been closed for replies.

    3 replies

    D
    Dan_Bracuk
    Inspiring
    January 9, 2009
    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.
    N
    Novice2002
    Known Participant
    January 9, 2009
    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"?
    D
    Dan_Bracuk
    Inspiring
    January 9, 2009
    How about
    select case when activity_id = 2 then activity_date else null end activity_date ?

    T
    trojnfnAuthor
    Inspiring
    January 9, 2009
    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 ?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices