How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>