Select top 60 where date is a year ago from today

I have a table which is holding historical data.  I am working on doing a comparison of recent 30 days versus same 30 days 1 year ago.  There are 2 readings from each day and I do a formula to compute the daily total.

So basically I need to get all records that are 30 days from today from one year ago.
martyuniAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
to get dates from 1 year ago use

select *
from table
where mydate < dateadd(YEAR, -1, getdate())

you can combine to make 1year and 30 days

eg
select *
from table
where mydate < dateadd(DAY, -30, dateadd(YEAR, -1, getdate()))

or if you need between

select *
from table
where mydate between dateadd(DAY, -30, dateadd(YEAR, -1, getdate())) and dateadd(YEAR, -1, getdate())
0
 
deightonprogCommented:
date here is dob

select * from (select top 30 * from yourtable order by dob desc) x
union
Select * from (select top 30 * from yourtable where dob < dateadd(year,-1,getdate()) order by dob desc)  y
order by dob
0
 
jo_mCommented:
declare@ prevdate datetime
declare @prevday datetime

set@Prevday = dateadd(Day,-30 getdate())
set@ Prevdate = dateadd(year,-1,@prevday)


Select  top 30 from  tblnm  where  dtfld =@prevdate


this appeared to work  on some data I had  available to test with

jo
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
martyuniAuthor Commented:
So if I do this query:

eg
select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, getdate())

That gets me 30 days from a year ago, which is what I want.  The only thing I need to change is that I don't want the data from today's date from one year ago.  Does that make sense?
0
 
dougaugCommented:
See if this is what you want:

declare @d date

set @d = '2011-10-17' -- or your desired date in format YYYY-MM-DD

select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, @d)
0
 
Ephraim WangoyaCommented:
You can start by striping the date from todays date, then subtracting a year

 
declare @datevar datetime

set @datevar = DATEADD(DD, 0, datediff(dd, 0, getdate()))

select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, @datevar)

Open in new window


If you want to combine the whole thing in one query

 
select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, dateadd(DD, 0, datediff(dd, 0, getdate())))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.