View Single Post
  #6 (permalink)  
Old December 17th, 2008, 10:27 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

What DB's is it *NOT* true in, Doug? And are you saying there are versions of SQL Server where it is not true?

I don't see how a DB could be ANSI-compliant and not have that situation exist. No matter how the DATETIME value is stored. (Of course, if the *USER* chooses to store the date and time in separate fields, that's a completely different issue/question/solution. But doing that creates its own complexities and I usually recommend against it.)

Anyway, all I wrote is also true in Access (or any JET-accessed DB or pseudo-db, such as Excel or text files).

And of course another way to solve the problem is to convert the date-and-time value to date-only as part of the BETWEEN:
Code:
Access/JET:
... WHERE DATEVALUE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
MySQL:
... WHERE DATE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
SQL Server:
... WHERE CONVERT(DATETIME, CONVERT(VARCHAR, DateColumn, 112), 112) BETWEEN '1/1/2009' AND '1/31/2009'
Only SQL Server is too stupid to give us a nice handy function to do this (and similarly omits other handy datetime functions).

Anyway...You do *NOT* want to use a function like that if the field is indexed, because you just caused a full scan of all values in the index. If you use the >= and < solution that I showed, you can take full advantage of an index on the column.

Last edited by Old Pedant; December 17th, 2008 at 10:34 PM..
Reply With Quote