Tuesday, February 27, 2007

A Date With DATETIME

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 Itzik Ben-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.

For further studies :

36 comments:

Anonymous said...

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) ?

Buy Cialis said...

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

pharmacy said...

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.

отели в барселоне said...

So much useful data for everyone!

Anonymous said...

buy xanax no prescription xanax class of drug - xanax drug interactions

Anonymous said...

generic xanax normal dosage xanax prescribed - 1mg xanax urine

Anonymous said...

xanax for anxiety order xanax pakistan - drug interactions wellbutrin xanax

Anonymous said...

buy tramadol cod tramadol for dogs how long does it take to work - buy tramadol online usa

Anonymous said...

buy tramadol online buy tramadol no prescription cheap - tramadol overdose how much does it take

Anonymous said...

buy tramadol online tramadol hcl nsaid - que es tramadol hcl 50 mg

Anonymous said...

legal buy tramadol online order tramadol with mastercard - buy generic tramadol no prescription

Anonymous said...

xanax online xanax clearance drug screen - xanax side effects shaking

Anonymous said...

buy tramadol without a script tramadol withdrawal day 8 - tramadol-no-prescription

Anonymous said...

buy tramadol online tramadol hydrochloride - ultram vs tramadol generic

Anonymous said...

buy tramadol overnight cod tramadol 50 mg biogaran - tramadol no prescription overseas

Anonymous said...

generic xanax cheap xanax india - xanax side effects 2011

Anonymous said...

buy tramadol online tramadol online canadian pharmacy - long does 50mg tramadol take kick

Anonymous said...

generic xanax xanax 2 mg snorting - 3mg xanax effects

Anonymous said...

buy tramadol online tramadol for dogs safe - buy tramadol 6914

Anonymous said...

buy carisoprodol carisoprodol 350 mg withdrawal - 350 mg carisoprodol generic soma

Anonymous said...

carisoprodol without prescription ketamine carisoprodol - buy carisoprodol online no prescription

Anonymous said...

buy tramadol online tramadol overdose how many - buy tramadol online from usa

Anonymous said...

xanax online ativan vs xanax for anxiety - xanax drug reference

Anonymous said...

buy generic cialis no prescription buy cialis online mastercard - price for cialis 10mg

Anonymous said...

20000 :) Order Zithromax - generic zithromax cost http://www.zithromaxhowtobuy.net/#generic-azithromycin, [url=http://www.zithromaxhowtobuy.net/#order-zithromax]Azithromycin No Prescription[/url]

Anonymous said...

buy tramadol tramadol 50mg much - tramadol 50mg for dogs usa

Anonymous said...

http://landvoicelearning.com/#44827 tramadol withdrawal clonidine - tramadol online american pharmacy

Anonymous said...

buy tramadol tramadol buy online no prescription mastercard - tramadol hcl effects

Anonymous said...

buy tramadol online tramadol buy usa - tramadol for dogs dosage chart

Anonymous said...

buy tramadol online tramadol addiction management - buy cheap tramadol cod

Anonymous said...

buy tramadol buy tramadol online overnight delivery - tramadol 50mg generic for ultram

Anonymous said...

lorazepam drug ativan withdrawal protocol - ativan with alcohol side effect

Anonymous said...

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-

Anonymous said...

ways to buy ativan online ativan for panic attacks - ativan beer

Anonymous said...

buy tramadol no prescription overnight tramadol 93 58 information - tramadol high grasscity

Anonymous said...

http://ranchodelastortugas.com/#30416 what does xanax and alcohol feel like - xanax street value 2mg