datetime comparisons in T-SQL aren't always a walk in the park. One of the most annoying things that someone would need to overcoming is filtering solely on the year, month, and day component of a datetime field. This is further complicated by how .NET initializes a DateTime object.
When a DateTime object is instantiated without secifying the time, it is set to 12:00AM (midnight). When you pass the DateTime object to an SqlParameter, the following is sent over the wire:
exec GetContracts @CreationDate = 'Aug 13 2004 12:00:00:000AM'
A further complication is that even if you pass creationDate.ToShortDateString() (assuming of course that your DateTime object in code is 'creationDate') to the Value of your SqlParameter, the afortementioned value is still passed to the stored procedure. This is because the stored procedure parameter is typed as datetime. Now, how do you overcome this?
With values in the table such as '2004-08-13 13:02:44.767', you'll never be able to match 'Aug 13 2004 12:00:00:000AM', and therefore no rows will be returned. The way I have overcome this problem is to make use of DATEDIFF and the dayofyear DATEPART. By using the following in your WHERE clause:
DATEDIFF(dayofyear, @CreationDate, Contracts.CreationDate) = 0
This will return 0 whenever the day of the year is the same in both the table, and the stored procedure parameter. Any other day will either cause DATEDIFF to return a non-zero value. You can also use this if you want everything from this date and on (or before) by changing the comparitor to > or < as appropriate.
Sproc on!
Leave a comment