SQL Server, Query Last month, always

Below is sample VBSCRIPT code which I think does what you would like. SQL has very similar functions, and this should port relatively easily:

currentdate = Now()
prevmonth = month(dateadd("m",-1,currentdate))
prevmonthlastday = day(dateadd("d",-1,cdate(month(currentdate) & "/01/" & year(currentdate))))
prevmonthlastyear = year(dateadd("d",-1,cdate(month(currentdate) & "/01/" & year(currentdate))))

StartDate = prevmonth & "/1/" & prevmonthlastyear
EndDate = prevmonth & "/" & prevmonthlastday & "/" & prevmonthlastyear

Hope this helps!

Curtis
StreamLogic Inc.
www.streamlogicinc.com

declare @FirstDayOfMonth DateTime, @FirstDayOfLastMonth DateTime

set @FirstDayOfMonth = convert(datetime, convert(char(7), getdate(), 120) + '-01')
set @FirstDayOfLastMonth = dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))

select *
from NOFEAR.dbo.ivhsth
where IV_DATE >= @FirstDayOfLastMonth
and IV_DATE < @FirstDayOfMonth

I almost get this to work, but it shows some days from this month.

Try this as part of your query where the field name is the date field:
WHERE MONTH(TableName.FieldName) = (MONTH(GETDATE()-DAY(GETDATE())))
AND
YEAR(TableName.FieldName) = YEAR(GETDATE()-DAY(GETDATE()))

Instead of using procedural code to compute temporal values, you can build a Calendar table and join to it:

CREATE VIEW LastMonthReportRange (start_date, end_date)
AS
SELECT start_date, end_date
FROM Calendar
WHERE cal_date = CURRENT_TIMESTAMP;

You get portable code, can use indexes, etc. And the Calendar table can contain other information such as holidays, fiscal calendars, etc.

This should work every time...

Use this clause to select the invoice dates

DatePart("m", ) = DatePart("m", DateAdd("m", -1, getdate())) AND DatePart("yyyy", ) = DatePart("yyyy", DateAdd("m", -1, getdate()))

where is the date in your dataset for which you need to select last month's dates.

Hope this helps.