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

x
?
Solved

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

Posted on 2009-05-12
7
Medium Priority
?
1,056 Views
Last Modified: 2012-05-06
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
Comment
Question by:Saroj13
7 Comments
 
LVL 6

Expert Comment

by:bull_rider
ID: 24367441
Do you want all of them in one single sql statement? I am surprised.
0
 
LVL 1

Accepted Solution

by:
Samoin earned 800 total points
ID: 24367457
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
 
LVL 6

Assisted Solution

by:bokist
bokist earned 600 total points
ID: 24367539
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Saroj13
ID: 24368305
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
 
LVL 6

Assisted Solution

by:bokist
bokist earned 600 total points
ID: 24369195
select * from my_table
where datepart(year, my_date) = datepart(year, getdate())
   and  datepart(month, my_date)  := datepart(month, getdate())  - 1

0
 
LVL 6

Expert Comment

by:bokist
ID: 24369229
in third line didn't appear greater sign.
something is wrong with my keyboard.
third line is greater or equal
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 600 total points
ID: 24370260

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

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Loops Section Overview
Screencast - Getting to Know the Pipeline
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question