comparing dates

I try to compare dates in a NON EXISTS expression like this:
SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
DatePackedForShipment
FROM ArticleSerialNumbersTempEgersund a
WHERE (NOT EXISTS
(SELECT b.ArticleNo, b.SerialNumber,
b.DateShipped, b.DateAssembled, b.DatePackedForShipment
FROM ArticleSerialNumbers b
WHERE (b.ArticleNo = a.ArticleNo) AND
(b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all the
other dates) but still all records show. If i use only ArticleNo and
SerialNumber then it is no problem. What is it i am missing here?
I use SQL 2000 std server with SP4
Best regards
TrondTry DATEDIFF ( datepart , startdate , enddate ) function.
/Karin
"Trond Hoiberg" wrote:

> I try to compare dates in a NON EXISTS expression like this:
> SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
> DatePackedForShipment
> FROM ArticleSerialNumbersTempEgersund a
> WHERE (NOT EXISTS
> (SELECT b.ArticleNo, b.SerialNumber,
> b.DateShipped, b.DateAssembled, b.DatePackedForShipment
> FROM ArticleSerialNumbers b
> WHERE (b.ArticleNo = a.ArticleNo) AND
> (b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
> CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
> CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
> BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
> I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all th
e
> other dates) but still all records show. If i use only ArticleNo and
> SerialNumber then it is no problem. What is it i am missing here?
> I use SQL 2000 std server with SP4
> Best regards
> Trond
>
>|||> SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
> DatePackedForShipment
> FROM ArticleSerialNumbersTempEgersund a
> WHERE (NOT EXISTS
> (SELECT b.ArticleNo, b.SerialNumber,
> b.DateShipped, b.DateAssembled, b.DatePackedForShipment
> FROM ArticleSerialNumbers b
> WHERE (b.ArticleNo = a.ArticleNo) AND
> (b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
> CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
> CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
> BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
> I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all
> the other dates) but still all records show. If i use only ArticleNo and
> SerialNumber then it is no problem. What is it i am missing here?
Why are you casting all of these related columns? Are they of different
datatypes? What do you hope to accomplish with the casting?|||I did compare without casting but that had same result. The columns i try to
compare is datetime but value stored in them is 01.10.2005. The date is
inserted from a Access frontend and the developer there has done it this way
for some reason. It is strange tho and i really think it is annoying. When i
make a query in Enterprise manager itself suggest to use CONVERT by
simplyadding it to the expression when i enter 01.10.2005 in criteria field.
So i was asking what is it i am missing here, because it is obviously that i
do and i dont get it.
Best regards
Trond
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uoCR7PDIGHA.916@.TK2MSFTNGP10.phx.gbl...
> Why are you casting all of these related columns? Are they of different
> datatypes? What do you hope to accomplish with the casting?
>|||
>I did compare without casting but that had same result. The columns i try
>to
> compare is datetime but value stored in them is 01.10.2005. The date is
There should be no need to cast columns of the same datatype during the
comparison. Datetime values are not stored in columns (or variables) of
datetime datatype in a format that is readable or that has formatting
characters. Perhaps the values stored in the compared columns are not
exactly the same?

> inserted from a Access frontend and the developer there has done it this
> way for some reason. It is strange tho and i really think it is annoying.
> When i make a query in Enterprise manager itself suggest to use CONVERT by
> simplyadding it to the expression when i enter 01.10.2005 in criteria
> field.
No idea what this means. However, you should avoid using EM as an editing
tool. Use QA where you can control exactly what query is used to view
information as well as what statement is used to insert / update / delete
rows.

> So i was asking what is it i am missing here, because it is obviously that
> i do and i dont get it.
Show the DDL for the tables involved, as well as some sample data that
illustrates the problem. Otherwise, we cannot offer any useful suggestions.sqlsql