Link to home
Start Free TrialLog in
Avatar of oranco
oranco

asked on

SQL Query pulling up dynamic date range

We need to pull up records with a date range from 2 months ago from today to 1 yr ago from today. What is the SQL formula for this?

thx
Avatar of ptjcb
ptjcb
Flag of United States of America image

You will need a column with the date information to choose from. There is no formula, it must be built in your table data.

WHERE date_column between 20071001 and 20080101

Avatar of Guy Hengel [angelIII / a3]
as you say "dynamic", this should change automatically every month?

assuming you want the full months only...


where date_column < dateadd(month, -1, dateadd(day, 1-datepart(day,getdate()), getdate()))
and date_column >= dateadd(month, -12, dateadd(day, 1-datepart(day,getdate()), getdate()))

Open in new window

Avatar of oranco
oranco

ASKER

oh i realise that i need a date field but i need a dynamic range which today would pull up people from 2nd Jan 2007 - 2nd Nov 2007 but tomorrow will pick up people from 3rd Jan 2007 - 3rd Nov 2007
Select * from TABLENAME
Where DateColumn Between dateadd(month, -2, getdate()) And dateadd(year, -1, getdate())
Avatar of oranco

ASKER

angellll, does your solution fit in with my clarification?
ASKER CERTIFIED SOLUTION
Avatar of Ashish Patel
Ashish Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
here we go with the clarification adjustment:
where date_column < dateadd(month, -2, getdate())
and date_column >= dateadd(month, -12, getdate())

Open in new window

Avatar of oranco

ASKER

you da man!