Query records in last 12 months

Question

Post reply

Query records in last 12 months

Tuan H. Le

SSC Eights!

Points: 806
More actions
August 2, 2006 at 11:33 am

#115004

Hi,
In my query it needs to return records which were created in past 12 months. I'm not familiar with date calculation. Could someone please help with the query
SELECT * FROM my_table where created_date < GETDATE() - 12 Months
Much thanks!
Tuan

Viewing 9 posts - 1 through 8 (of 8 total)

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

SELECT * FROM my_table where DATEDIFF(month,created_date, GETDATE()) <= 12

check out DATEDIFF in BOL for more options.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 2

Dinakar has a solution, but the way he wrote it, the database can't use any index for the date column.

Use this instead

SELECT * FROM my_table where created_date >= DATEADD(month, -12, GETDATE())

N 56°04'39.16"
E 12°55'05.25"

Tuan H. Le SSC Eights! Points: 806 More actions · Answer 3

That works! Thanks very much to both Peter and Dinakar!

Tuan

emandl Newbie Points: 3 More actions · Answer 4

In Order to Get the last 12 month including days

SELECT * FROM my_table where DATEDIFF(DAY, created_date , GETDATE()) <= 365

ratbak Ten Centuries Points: 1333 More actions · Answer 5

Be careful of leap years.

Using 365 days could give a different result than using 12 months or 1 year in a leap year -- specifically on February 29th. Using month or year will include February 28th of the prior year. Using day would start at 03/01. (OK if that's really the intent)

Regardless, you would also want to consider the indexability concern mentioned by SwePeso, and convert to use comparison of current_date to dateadd -- e.g.,

SELECT * FROM my_table where created_date >= DATEADD(day, -365, GETDATE())

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 6

emandl wrote:

In Order to Get the last 12 month including days
SELECT * FROM my_table where DATEDIFF(DAY, created_date , GETDATE()) <= 365

I'm going to be a wee bit more blunt that Ratback... DON"T USE SUCH CODE!

You've destroyed any chance of getting an index seek on the created_date and 1 out of every 4 years in this century has a different number of days. You're also not including whole days if the created_date has a time element to it and, even if it didn't, it won't be bullet-proof if someone changes it and you need it to include whole days.

And, last time I checked, 12 months is actually a year in the Gregorian Calendar that most of us use. Why are we screwing around with months, never mind days? Use Years if the requirement is 12 months. And, if it IS actually supposed to be months, should those be WHOLE months or partial months?

So, if the requirement is actually a "year ago today" and you want it to take leap years into account (and you probably should) and you want WHOLE days to be considered (and you probably should) AND you want it to NOT do a table or index scan and do a seek, instead (and you probably should!), then the following code is one of the ways you can pull all of that off.

 SELECT Whatever
   FROM dbo.my_table
  WHERE created_date >= DATEADD(yy,-1,CONVERT(DATE,GETDATE()))
  --AND created_date <  CONVERT(DATE,GETDATE()) --Uncomment this if you want to exclude today, which is a partial day.

And, yeah... consider always using the two part naming convention for user objects. It'll save your butt one of these days.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

emandl Newbie Points: 3 More actions · Answer 7

emandl

Newbie

Points: 3

January 19, 2022 at 7:41 am

#3974543

Thanks a lot for the feedback will do that!

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 8