June 3, 2009 at 3:14 pm
I need a query that retrieves data from the previous month only. I'm creating a package that will run every month and needs to pull data from the previous month and outputs to an Excel file. I can do the rest, but I'm having trouble figuring out how to write the query. This would be so much easier if each month had equal number of days!! Aargh!
Any help is greatly appreciated!
June 3, 2009 at 4:17 pm
I think you'll need to provide a bit more information before anyone can help you. Working with dates is pretty straight forward with SQL, but we need to know more about your problem. Can you post your table DDL, some sample data, and your query that you tried that does not work?
For good reference on how to form a good post, read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 3, 2009 at 4:31 pm
I just need to run a query that will pull data from the past month. So basically:
SELECT * FROM Output
WHERE Date BETWEEN '01/01/2009' AND '01/30/2009'
But wanting to automate it, I don't want to have to manually edit the query every month to change it from January to February, etc. I want to schedule the package to run on the first day of each month to pull data from the previous month. I'm working with SQL Server 2005 and Visual Studio 2005. Is that enough info?
June 4, 2009 at 12:48 am
Declare @month int, @year int
Declare @dt Datetime
Set @dt = getdate()
set @month = month(@dt) - 1
set @year = year(@dt)
if @month = 0 --january
begin
Set @month = 12
Set @year = @year - 1
End
SELECT * FROM Output
WHERE Month(Date) = @month and Year(Date) = @year
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 4, 2009 at 7:31 am
Cool, thanks! This one looks less complicated than Datepart. Thanks again for your help!
June 4, 2009 at 8:35 am
ryan213 (6/3/2009)
I just need to run a query that will pull data from the past month. So basically:SELECT * FROM Output
WHERE Date BETWEEN '01/01/2009' AND '01/30/2009'
But wanting to automate it, I don't want to have to manually edit the query every month to change it from January to February, etc. I want to schedule the package to run on the first day of each month to pull data from the previous month. I'm working with SQL Server 2005 and Visual Studio 2005. Is that enough info?
Here is a another way:
select
*
from
dbo.Output
where
[Date] >= dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) and
[Date] < dateadd(mm, datediff(mm, 0, getdate()), 0);
June 4, 2009 at 9:14 am
Just a note to add. If you have an index on Date, Lynn's query would be the preferred method as it should result in an index seek. The first method of using the MONTH function on the Date column would result in an index scan. Just something to consider.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply