compare 2 date part of datetime data

[iccsi]

I want to compare date part of 2 datetime type field.
I think the best way is to cast to date part data only, since if I
compare whole data which include time part of value will not get right
result.
My question is which data type is best way to do like char(20) or
integer?
If I use integer than i can just compare 2 value. The only thing is
does SQL cast function consider datetime is a float and date part is
inter part of the value?

Your information and help is great appreciated,

iccsi

[Erland Sommarskog]

Post by iccsi
I want to compare date part of 2 datetime type field.
I think the best way is to cast to date part data only, since if I
compare whole data which include time part of value will not get right
result.

If you are on SQL 2008:

convert(date, datetimecol1) = convert(date, datetimecol2)

[iccsi]

Thanks for message and help,
I use SQL 2000

iccsi

Post by iccsi
I want to compare date part of 2 datetime type field.
I think the best way is to cast to date part data only, since if I
compare whole data which include time part of value will not get right
result.

  convert(date, datetimecol1) = convert(date, datetimecol2)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[iccsi]

I use SQL Server 200 for now.
Date type does not exist in SQL Server 2000.
I tried to use convert(int, datetimecol1) = convert(int,datetimecol2).
It looks like working,

Thanks for the message and help,

iccsi

Post by iccsi
I want to compare date part of 2 datetime type field.
I think the best way is to cast to date part data only, since if I
compare whole data which include time part of value will not get right
result.

  convert(date, datetimecol1) = convert(date, datetimecol2)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by iccsi
I use SQL Server 200 for now.
Date type does not exist in SQL Server 2000.
I tried to use convert(int, datetimecol1) = convert(int,datetimecol2).
It looks like working,

Please always specify which version of SQL Server you are using.

Converting to int works, although I would prefer

convert(char(8), datetimecol, 112)

If one table is bigger than the other and there is an index, this may
be better:

datetimecol1 >= convert(char(8), datetimecol2, 112) AND
datetimecol1 < convert(char(8), datetime(DAY, 1, datetimecol2), 112)