General discussion

Locked

SQL Server, Query Last month, always

By akalinowski ·
I am trying to create a view in microsoft sql server 2005, where it pulls in invoice details for last month, i can use the between command and get it working just fine, but i have to change it every month, the view is used for a report that runs on an asp web page

my question is simply how do i get the view to always pull last month only

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by StreamLogic In reply to SQL Server, Query Last mo ...

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

Collapse -

by akalinowski In reply to

Poster rated this answer.

Collapse -

by akalinowski In reply to SQL Server, Query Last mo ...

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.

Collapse -

by ekerr In reply to SQL Server, Query Last mo ...

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()))

Collapse -

by akalinowski In reply to

Poster rated this answer.

Collapse -

by jcelko212 In reply to SQL Server, Query Last mo ...

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.

Collapse -

by akalinowski In reply to

Poster rated this answer.

Collapse -

by dporter In reply to SQL Server, Query Last mo ...

This should work every time...

Use this clause to select the invoice dates

DatePart("m", <InvoiceDate&gt = DatePart("m", DateAdd("m", -1, getdate())) AND DatePart("yyyy", <InvoiceDate&gt = DatePart("yyyy", DateAdd("m", -1, getdate()))

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

Hope this helps.

Collapse -

by dporter In reply to

OOPS...the "m" and "yyyy" should not be quoted if you are using this in a SQL Server Query or Transact-SQL Code. This is the VB version.

Collapse -

by akalinowski In reply to

Poster rated this answer.

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums