Post reply

Datetime <> For Update

  • March 26, 2009 at 7:17 pm

    #202771

    I'm running an update script that I can't get to recognize changes from null to a not null. Table A is updated from Table B based on an ID. If the Date in Table A is Null, it won't recognize an "is not equal to" (<>) comparison if the corresponding record in Table B has a value in it's date field.

    Here's an example of the query that I'm running:

    Select A.ID

    ,A.Date

    ,A.Value From

    Table1 as A

    JOIN

    Table2 as B

    ON

    A.ID=B.ID

    WHERE

    A.Date<>B.Date

    This seems like a simple issue, but I just can't get it to work. Is there some unusual behavior with comparing null values in a Datetime field?

  • March 26, 2009 at 8:23 pm

    #967183

    that's the expected behavior:

    if you compare anything to null, the results are undefined...

    so you either want to add a where clause like ...AND A.DATE IS NOT NULL AND B.DATE IS NOT NULL

    or convert them so nulls get compared with an assummed value:

    you'll want to do something like ISNULL(a.Date,'01/01/1900') <> ISNULL(b.Date,'01/01/1900')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 26, 2009 at 8:33 pm

    #967185

    Thanks, I think I did something similar with a COALESCE comparing them.

    COALESCE(A.Date,0)<>COALESCE(B.Date,0)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply