Datepart (mm, '01/01/2009') -1

Question

Datepart (mm, '01/01/2009') -1

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
September 1, 2009 at 7:16 am

#156159

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass