tag:blogger.com,1999:blog-16771259.post116162457898789915..comments2024-03-07T03:02:45.934-08:00Comments on SQL Server Code,Tips and Tricks, Performance Tuning: Do You Know How Between Works With Dates?Unknownnoreply@blogger.comBlogger6125tag:blogger.com,1999:blog-16771259.post-1161715140778642292006-10-24T11:39:00.000-07:002006-10-24T11:39:00.000-07:00as someone probably said already, if your operand ...as someone probably said already, if your operand is a timestamp, then your between values should be a timestamp too - if they are a date, they will be converted to a timestamp with time 00:00:00 - so in terms of dates, the right hand side will become exclusive, whereas usually it is inclusive.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1161714498987544622006-10-24T11:28:00.000-07:002006-10-24T11:28:00.000-07:00Conversion on the left side of an operator will fo...Conversion on the left side of an operator will force an table/index scan instead of an index seek. This will hurt performance,You can verify that here-->http://sqlservercode.blogspot.com/2005/12/sql-query-optimizations.htmlDenishttps://www.blogger.com/profile/13745938552201273794noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1161714321994139512006-10-24T11:25:00.000-07:002006-10-24T11:25:00.000-07:00Good warning, but easily fixedWHERE date(dbdate) B...Good warning, but easily fixed<BR/><BR/>WHERE date(dbdate) BETWEEN '2006-10-01' AND '2006-10-02'Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1161644908499983402006-10-23T16:08:00.000-07:002006-10-23T16:08:00.000-07:00Hi anonymous,Your recommendation to use DateColumn...Hi anonymous,<BR/><BR/>Your recommendation to use DateColumn between '2006-10-01' and '2006-10-02 23:59:59' can fail.<BR/><BR/>For smalldatetime, 23:59:59 has to be rounded to the nearest full minute - and you're back at 2006-10-03, midnight.<BR/>For datetime, there's no rouding, but values from 2006-10-02 23:59:59.003 up to 2006-10-02 23:59:59.997 will be missed.<BR/><BR/>Also, neither '2006-10-02' nor '2006-10-03 23:59:59' are guaranteed safe date or datetime formats. Better use '20061002' (no dahses) and '2006-10-03T23:59:59' (dashes, colons, AND uppercase T).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1161627293528146142006-10-23T11:14:00.000-07:002006-10-23T11:14:00.000-07:00That's true and I am aware of that. BTW SQL Server...That's true and I am aware of that. BTW SQL Server only has datetime columns not date columns (time is always included). I am pointing this out because I came across code like this many times and then people would spend hours to find why their reports were not correct. this is just a warning that's allDenishttps://www.blogger.com/profile/13745938552201273794noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1161627042187882412006-10-23T11:10:00.000-07:002006-10-23T11:10:00.000-07:00Seems obvious to me. Your database field is a date...Seems obvious to me. Your database field is a datetime, not a date field. Thus, '2006-10-02' is equivalent to '2006-10-02 00:00:00'. Try using:<BR/>DateColumn between '2006-10-01' and '2006-10-02 23:59:59'Anonymousnoreply@blogger.com