September 1, 2009 at 7:16 am
I'd like to collect data for a report that goes back three months using the datepart function and month as my interval. As an example if I wrote a query the following way:
select *
from table1
where
datepart(mm, DateCol1)
between datepart(mm, getdate()) -3 and datpart(mm, getdate())
I would get the dataset I'm looking for, however; come January 2010 when the query runs
between datepart(mm, '01/01/2010') -3 and datpart(mm, '01/01/2010')
the datepart interval is going to return a negative integer
ie, select datepart(mm, '01/01/2010') -1 returns the value: -1
using the datepart(mm, getdate()) function, is there a way to go back and retrieve months from previous years?
Thank you in advance.
September 1, 2009 at 7:24 am
Not datepart, but try dateadd.
p.s. the way you've written those queries is highly inefficient. By putting a function on the column, you're forcing a table scan, SQL cannot use indexes properly. Also, you can't go over the year boundries.
Try something like this rather.
select <Column List>
from table1
where
DateCol1 between dateadd(mm, -3, getdate()) AND getdate()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 7:35 am
GilaMonster (9/1/2009)
Not datepart, but try dateadd.p.s. the way you've written those queries is highly inefficient. By putting a function on the column, you're forcing a table scan, SQL cannot use indexes properly. Also, you can't go over the year boundries.
Try something like this rather.
select <Column List>
from table1
where
DateCol1 between dateadd(mm, -3, getdate()) AND getdate()
Thanks Gila, that was actually quite simple, I appreciate the insight and suggestion.
September 1, 2009 at 7:43 am
See this for some tricks on working with datetime values
http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply