Software development, .Net, SQL Server, TDD, Agile, Community and other Odds and Sods
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Tuesday, May 22, 2007
SQL Server: Compare Date Part of DateTime Value
This is not new, but I wanted to record it for my own future reference.
Developers are sometimes caught when creating WHERE clauses that compare SQL Server datetime column values that have a time portion, such as '2007-05-20 21:32:33.437' as opposed to '2007-05-20 00:00:00'. For instance, if you were to select all orders that had an OrderDate >= '2007-05-10 21:32:33.437', then all rows having the value of that date but a smaller time portion would fail the test.
There are quite a few ways to extract just the date portion of a datetime value, but several suffer from the drawback that they apply a formula to the column being tested and greatly reduce the possibility that an index can be used (i.e. non searchable arguments). The following converts @SomeDateParam to have a zero time portion in a test of all order dates greater than or equal to a given datetime:
WHERE DateColumn >= DATEADD(day, DATEDIFF(day, 0, @SomeDateParam), 0)
Of course, not every comparison criteria can be SARG'ed that way, but many can. How you apply such formulas depends on the type of comparison.
MSN, Email: mitch døt wheat at gmail.com