date question

Question

Post reply

date question

rs-337036

Ten Centuries

Points: 1105
More actions
March 4, 2010 at 10:20 am

#230462

I have a table called order and i have a field called orderdate as datetime
i want to check if the orderdate falls between the last 13 months from today's date. how do i check that.
This is how it stores the info in date 8/7/2008 11:35:04 AM

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

SELECT col

FROM table

WHERE orderdate >= DATEADD(month,-13,getdate())

AND orderdate < getdate ()

or, if you'd need to include the full month:

SELECT col

FROM table

WHERE orderdate >= DATEADD(month,DATEDIFF(month,0,getdate())-13,0)

AND orderdate < getdate ()

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

adams.squared SSCrazy Points: 2675 More actions · Answer 2

For the last 13 months, I would use

where datediff(mm, OrderDate, getdate())<=13

edit: Mine will include the whole month, so for today it would have 13 months and 4 days to show all of Feb 2009.

LutzM SSC Guru Points: 107049 More actions · Answer 3

adams.squared (3/4/2010)
For the last 13 months, I would use
where datediff(mm, OrderDate, getdate())<=13
edit: Mine will include the whole month, so for today it would have 13 months and 4 days to show all of Feb 2009.

When coding that way you prevent SQL server from using possibly existing indexes on OrderDate.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

adams.squared SSCrazy Points: 2675 More actions · Answer 4

lmu92 (3/4/2010)
When coding that way you prevent SQL server from using possibly existing indexes on OrderDate.

I was not aware of that. Thanks