Hi Friends..
I have seen that comparing a datetime column in a table in SQL Server database, that has both date and time parts (e.g. 2012-12-05 10:37:36.897) with a date literal that has only date part is a not straight forward. Let me explain with an example:
Have created a temporary table with two date columns – From_Date and To_Date and inserted a couple of random date values. First output shows the table contents. Observe that the timestamps are also included with the date values.
Now declared a date variable and assigned a date value without timestamp. Then retrieved results from the table where From_Date is less than or equal to the date variable in two ways:
– once by converting the From_Date column into string taking the format “mm/dd/yyyy” (note the format style 101 in the convert function syntax) and then casting this date string back to datetime data type. This removes the timestamp from the datetime values.
– the other time by comparing the direct comparison between From_Date datetime column and datetime variable. This comparison compares the both date and time pieces of the From_Date column and the datetime variable, hence resulting in incorrect(less) number if results.
Hope that helps. Do leave your comments or questions, if any.. 🙂
