Compare Date portion of Datetime in SQL

I am working with SQL and I have two columns with datetime field.

What I want to do is just compare the date portion of the these datetime fields and see if there are different.

For example,

          col1                     |    col2
'2010-01-02 23:58:00.000'          | '2010-01-03 06:21:00.000' 
'2010-01-04 16:03:00.000'          | '2010-01-04 21:34:00.000' 

Row1's dates are different but row开发者_如何学编程2 are same

I am thinking something like datediff(day,col2,col1) < 1 //Then this is different else they are same.

Not sure how to parse the date then compare two fields.

abs(datediff(day, col1, col2)) > 0 

This should work in many databases:

DATE(col1) = DATE(col2)

By "using SQL" I assume you meant SQL Server.

For all records where col1 and col2 are different days, you can use !=

SELECT *
FROM TBL
WHERE DateDiff(d, col1, col2) != 0

Check out the DatePart function. It enables extracting day, month, year, etc. from a date, which appears is what you are asking about.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

If you are using SQL Server (version 2008 and above), you can compare just the date portions of the datetime fields by casting them to the DATE format:

CAST(MyDate AS DATE) >= CAST(TheOtherDate AS DATE)