Sql Query that will return last one month data, last two months data and last one week in sql server 2005

Hi,

I need a sql query that will return all data for last one month, last two months ans last one week.

I have one smalldatetime column. Alos, I m using SQl server 2005.

Thanks

Saroj13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SamoinConnect With a Mentor Commented:
Can you be little bit more specific? Any ways below is the query that might help you

SELECT * FROM [YourTableName]
WHERE [date_column] Between GETDATE() And DATEADD(MM,-1,GETDATE())  --To get data for last one month
And [date_column] between GETDATE() and DATEADD(MM,-2,GETDATE()) -- To get data for last two months
And [date_column] between GETDATE() and DATEADD(ww,-1,GETDATE()  -- To get data for last week
0
 
bull_riderCommented:
Do you want all of them in one single sql statement? I am surprised.
0
 
bokistConnect With a Mentor Commented:
Check this solution :

select * from my_table
where datepart(year, my_date) = datepart(year, getdate())
   and  datepart(month, my_date) = datepart(month, getdate()) // or getdate())-1,,, -2
   and  datepart(week, my_date) = datepart(week, getdate())

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saroj13Author Commented:
little change.

I want data for last one month.
Today is May12, I want data from may12 -april12, 2009.

I need the sql query for that.

thanks
0
 
bokistConnect With a Mentor Commented:
select * from my_table
where datepart(year, my_date) = datepart(year, getdate())
   and  datepart(month, my_date)  := datepart(month, getdate())  - 1

0
 
bokistCommented:
in third line didn't appear greater sign.
something is wrong with my keyboard.
third line is greater or equal
0
 
SharathConnect With a Mentor Data EngineerCommented:

select *
  from my_table
 where my_date between DATEADD(mm,-1,GETDATE()) and GETDATE()

Open in new window

0
All Courses

From novice to tech pro — start learning today.