[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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.
0
martyuni
Asked:
martyuni
1 Solution
 
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
 
deightonCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now