Comparing Smalldatetime column

I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
--
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
--
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SORRY FOR THE DOUBLE POST...
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
>it's still UNKNOWN.
>>--
>>David Portas
>>--
>>Please reply only to the newsgroup
>>--
>>
>>.
>.
>|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
>required results. It's not clear to me exactly what
result you're trying to
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
>you use it on dates then every date has to be cast as a
string. Best way to
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
>Here's some code to reproduce a result that *may* be what
you want:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
>rows you want to be included in your result. I should
have covered all the
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>