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
1,048 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 200 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 150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 150 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 40

Assisted Solution

by:Sharath
Sharath earned 150 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if and else in stored procedure 19 48
SQL Server Degrading on Write 13 67
How to order Highest and lowest value rows alternatively in SQL Server ? 4 52
sql query help 2 51
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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