Always store date / time data in a DATETIME Datatype. If you use, for example, Varchar you have no ability to perform date calculations or compare dates etc.Convert string date to datetime datatype usingCONVERT(datetime, MyStringDate, nnn)where "nnn" is a number representing the format (e.g. d/m/y or m/d/y). See Books Online for further details.To compare two dates which also include a time element on the basis of "whole day" comparison you need to check that the first is with a range of Midnight-night-before to Midnight-night-following.So 11/10/2005 05:32:46 PMis within the range of >= "20051110 00:00:00.000" AND < "20051111 00:00:00.000"To "round" a date, including time, to only a date-at-Midnight use:DATEADD(Day, DATEDIFF(Day, 0, MyDate), 0)this tortuous looking formula is the fastest available, because all the date calculation are maintained as DATETIME, rather than (say) converting to String and Back again, which is slower."I can work around by just comparing the datepart"Best not to use the Date's Parts. That is slow (particularly if the DateTime Column is indexed - applying a Function tot he DateTime Column will prevent the index being used)So for a worked example:DECLARE @strDate1 varchar(50), @strDate2 varchar(50)SELECT @strDate1 = '11/10/2005 05:32:46 PM', @strDate2 = '2005-11-10 17:32:46.893'DECLARE @Date1 datetime, @Date2 datetimeSELECT @Date1 = CONVERT(datetime, @strDate1, 101), -- 101 = mm/dd/yyyy @Date2 = CONVERT(datetime, @strDate2, 121) -- 121 = yyyy-mm-dd hh:mi:ss.mmmSELECT [@strDate1] = @strDate1, [@strDate2] = @strDate2, [@Date1] = @Date1, [@Date2] = @Date2, [tMidnightBefore] = DATEADD(Day, DATEDIFF(Day, 0, @Date2), 0), [tMidnightFollowing] = DATEADD(Day, DATEDIFF(Day, 0, @Date2)+1, 0)SELECT 'Yes'WHERE @Date1 >= DATEADD(Day, DATEDIFF(Day, 0, @Date2), 0) AND @Date1 < DATEADD(Day, DATEDIFF(Day, 0, @Date2)+1, 0)
Kristen 
Query with different time formats
Posted - 2007-10-12 : 15:16:16
