SQL - Formatting and comparing dates

When comparing dates it is important to remember that the DATETIME format has a timestamp that is included in the comparison.

So if you are comparing two dates and the timestamp does not match the comparison will be evaluated as false.

For example:  '2010-06-15 15:10:32.248' does not equal '2010-06-15 12:32:59.398'

To get around this you can change the timestamp value in your select query so it is 00:00:00.000.

Do this by using two CASTs and a FLOOR.

The first(inner) CAST will change the DATETIME to a FLOAT.  This keeps the values of the date and time but puts it in a format(FLOAT) to manipulate using the FLOOR.  The FLOOR rounds the time down to 00:00:00.000.

Now we need to CAST it back into a DATETIME and we are done.

The syntax for this query looks like this:

SELECT 

        CAST(

                FLOOR(

                        CAST(GETDATE() AS FLOAT)

                        )AS DATETIME

                )

If you would like to format the date so it doesn't have a timestamp at all you can CONVERT it into a VARCHAR.  I know people will say that this is the job of the front end but sometimes you need to pull these values into a report in SQL Server Management Studio and your result set is the front end, so to speak.  If you copy the set into Excel it can easily be formatted in Excel as well but if it is a query you run a lot you can write the query to do the conversion for you and save yourself the time in Excel.

The syntax to CONVERT the DATETIME into a VARCHAR looks like this:

SELECT 

        CONVERT(VARCHAR(10),GETDATE(),101) 

Here is a screen shot of these two queries at work: