Improve company productivity with a Business Account.Sign Up

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

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

0
Saroj13
Asked:
Saroj13
4 Solutions
 
bull_riderCommented:
Do you want all of them in one single sql statement? I am surprised.
0
 
SamoinCommented:
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
 
bokistCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
bokistCommented:
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
 
SharathData EngineerCommented:

select *
  from my_table
 where my_date between DATEADD(mm,-1,GETDATE()) and 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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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