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 I Gan, 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
36 comments:
Nice Site.
Informative too.
What about the new DST adjustments. what will be the impact? should we have to do fixes any existing code for sql server (like fixes for stored proc , triggers dealing with date/time) ?
This is very simple, but thanks you never know when you can learn something new, at least this happends to me for the person that I leass think could happen.
Good luck
Very good blog, I wanted something like this because I had considered that all the blogs were dead but now I have a new hope to read something different.
So much useful data for everyone!
buy xanax no prescription xanax class of drug - xanax drug interactions
generic xanax normal dosage xanax prescribed - 1mg xanax urine
xanax for anxiety order xanax pakistan - drug interactions wellbutrin xanax
buy tramadol cod tramadol for dogs how long does it take to work - buy tramadol online usa
buy tramadol online buy tramadol no prescription cheap - tramadol overdose how much does it take
buy tramadol online tramadol hcl nsaid - que es tramadol hcl 50 mg
legal buy tramadol online order tramadol with mastercard - buy generic tramadol no prescription
xanax online xanax clearance drug screen - xanax side effects shaking
buy tramadol without a script tramadol withdrawal day 8 - tramadol-no-prescription
buy tramadol online tramadol hydrochloride - ultram vs tramadol generic
buy tramadol overnight cod tramadol 50 mg biogaran - tramadol no prescription overseas
generic xanax cheap xanax india - xanax side effects 2011
buy tramadol online tramadol online canadian pharmacy - long does 50mg tramadol take kick
generic xanax xanax 2 mg snorting - 3mg xanax effects
buy tramadol online tramadol for dogs safe - buy tramadol 6914
buy carisoprodol carisoprodol 350 mg withdrawal - 350 mg carisoprodol generic soma
carisoprodol without prescription ketamine carisoprodol - buy carisoprodol online no prescription
buy tramadol online tramadol overdose how many - buy tramadol online from usa
xanax online ativan vs xanax for anxiety - xanax drug reference
buy generic cialis no prescription buy cialis online mastercard - price for cialis 10mg
20000 :) Order Zithromax - generic zithromax cost http://www.zithromaxhowtobuy.net/#generic-azithromycin, [url=http://www.zithromaxhowtobuy.net/#order-zithromax]Azithromycin No Prescription[/url]
buy tramadol tramadol 50mg much - tramadol 50mg for dogs usa
http://landvoicelearning.com/#44827 tramadol withdrawal clonidine - tramadol online american pharmacy
buy tramadol tramadol buy online no prescription mastercard - tramadol hcl effects
buy tramadol online tramadol buy usa - tramadol for dogs dosage chart
buy tramadol online tramadol addiction management - buy cheap tramadol cod
buy tramadol buy tramadol online overnight delivery - tramadol 50mg generic for ultram
lorazepam drug ativan withdrawal protocol - ativan with alcohol side effect
ujvzkv up ebpvi uyj [url=http://nikenzshoes.webs.com/]nike free [/url]
Fhbk wz krituix mdzhqjo [url=http://nikeshoesnzstore.webs.com/]nike shoes nz[/url]
Llyukd iv lzeza qq [url=http://cheapnike-shoes.webs.com/]cheap nike shoes[/url]
http://clashofclans.ca/wiki/User:Trxaqlf337
http://www.citizenmodel.net/?page_id=412&cpage=14#comment-28018
http://familyfitnessinformation.com/story.php?title=deciding-upon-quick-systems-in-nike-shoes-nz-
ways to buy ativan online ativan for panic attacks - ativan beer
buy tramadol no prescription overnight tramadol 93 58 information - tramadol high grasscity
http://ranchodelastortugas.com/#30416 what does xanax and alcohol feel like - xanax street value 2mg
Post a Comment