View Single Post
  #3 (permalink)  
Old December 15th, 2008, 10:33 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

But there is no reason you can't extend the first answer and do
SELECT * FROM tablename WHERE Year(DateColumn) = Year(getDate()) AND Month(DateColumn) = Month(getDate())

If the DateColumn is indexed, using BETWEEN and passing in @Date1 and @Date2 will be somewhat faster. But if the field is not indexed it probably won't make any noticable difference.

*** CAUTION! ***
If DateColumn actually contains both date and time, then doing
...WHERE DateColumn BETWEEN '1/1/2009' AND '1/31/2009'
will *NOT* give the right answer! You will end up omitting all records from 1/31/2009 except any that happen to have a time of midnight. If you DateColumn does include times, then you are better off doing
...WHERE DateColumn >= '1/1/2009' AND DateColumn < '2/1/2009'
Notice that we use >= for the start date but only use < for the end, thus ensuring we get all records from 1/31/2009 no matter what time they have.

Last edited by Old Pedant; December 15th, 2008 at 10:38 PM..
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
milton.fernando (December 16th, 2008)