Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Exclamation Unanswered: how can NULL be equal to today's date (was "Weird Query behavior plz help")

    I have a query that is behaving a little a weird. here is the example:

    i have 1 table in this table i have 2 columns wich are date and time

    DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
    ------------------------------ ---------------------------
    1/27/1997 2/27/1997
    1/1/2005 2/6/2005

    here is my query:

    BEGIN
    declare @datefin_flag datetime, @strip datetime
    SELECT @strip = dateadd(d,datediff(d,0,getdate()),0)
    SELECT @datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
    WHERE DATE_DEBUT_PERIODE_FISCALE < @strip AND DATE_FIN_PERIODE_FISCALE = @strip
    --select @datefin_flag
    --select @strip
    IF(@datefin_flag != @strip)
    RAISERROR('You cant run this',16,1)
    END

    Well this Query should return the raiserror it returns completes successfuly
    since todays date is not the same as the date in the database.
    if you select @datefin_flag it returns NULL and if you select @strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your code is checking for NOT equal. NULL is not equal to anything (including another NULL).

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Crap...didnt think of that how would i go about fixing this based on my expresion since if it looks in the table and doesnt find anything.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    IF(@datefin_flag != @strip OR @datein_flag IS NULL)
       RAISERROR('You cant run this',16,1)
    END
    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd write it this way...

    Code:
    IF NOT EXISTS( SELECT *
    		 FROM DM_LKP_CALENDRIER_PERIODE_F
    		WHERE DATE_DEBUT_PERIODE_FISCALE < dateadd(d,datediff(d,0,getdate()),0) 
    		  AND DATE_FIN_PERIODE_FISCALE   = dateadd(d,datediff(d,0,getdate()),0)
    	     )
    			RAISERROR('You cant run this',16,1)
    			END

    But I'd handle the raise a little differently

    http://weblogs.sqlteam.com/brettk/ar...5/25/1378.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Thanks alot Pat worked fine i realy apreciated.

  7. #7
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    thanks also Brett that is a realy good alternative

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your welcome...

    But it's not really an alternative....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •