Web Development

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

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.

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.

Related Discussions

Related Forums