Consider a simple(!?) query executed against the SQL Server 2005 sample database 'AdventureWorks'
SELECT soh.SalesPersonID, sum(sod.LineTotal) as amount
FROM sales.SalesOrderHeader soh
JOIN sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.Status = 5 -- complete
and soh.OrderDate >= '20040101'
GROUP by soh.SalesPersonID
Does it work fine...? Definitely it will work fine because it is a well tested by an author and a corporate database developer and architect-LOUIS DAVIDSON's Query illustrated in the book Apress : Pro SQL Server 2005. LOUIS DAVIDSON is one among the group of authors and he wrote the chapter 3 where this query is used.
I am not going to dig into this query and how it is working. Let us look in to the bold characters in the above query soh.OrderDate >= '20040101'
In this query , soh.OrderDate is datetime column and it is compared with a string expression '20040101' .
On focusing this portion, we have to recollect some simple things that we have already aware of, how SQL Server Stores a date value in available datetime datatypes.
- datetime : by consuming 8 byte integers for a single value, stores the number of days before or after the base date 1st January 1900 in the first four bytes, and the time of the day measured in 3 1/3 millisecond units after the midnight in the last four bytes.
- smalldatetime: by consuming 2 two bytes integers for a single value, stores the number of days after the base date 1st January 1900 in the first two bytes, and the time measured in minutes after midnight in the last two bytes.
Right!?
In the above query does not worry about the time. But for us in many cases as we are as a developer or a DBA it is a major factor.
Here in this query, the string expression '20040101' is implicitly converted to datetime, because according to the hierarchy of the precedence datetime has the higher precedence than the character string. This expression does not include time part, and the data in the table is also not time aware. What will happen to a query (which includes this type of string literals) that is so conscious about time part stored in the column?
Normally a datetime string literal which includes a milliseconds and when it is implicitly converted to datetime, it will be rounded to the nearest three hundredth milliseconds. If the same is converted to smalldatetime it will rounded to nearest minute.
The Author IGan, of the book 'MSPress: Inside Microsoft SQL Server 2005: T-SQL Programming' advise to explicitly convert the string to datetime using datetime functions supported by SQL Server (which perform correct calculations by taking leap year and other calendar calculations in account) whenever possible.
One more thing we have to consider in this regard is the Language Settings. SQL Server's datetime functions are depend on the language setting either the default setting of the Server or the Session. Session's setting has the higher precedence.
- Read MSPress: Inside Microsoft SQL Server 2005: T-SQL Programming by .
- http://msdn2.microsoft.com/en-us/library/ms191530.aspx
- http://msdn2.microsoft.com/en-us/library/ms187928.aspx
