SQL View, show last month always 1

[soutener]

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
(sorry if its the wrong board)

 

[gmmastros]

What is your definition of 'last month'. Seems silly, but the answer to your question depends on it.

It could be the last full calendar month, so if today is july 12, you want records from June 1 to June 30.

It could be the last 30 days.

It could be the last 4 weeks.

It could be from 1 month ago today until today. If Today is July 12, show June 12 to July 12.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[soutener]

last calender month

so if its july 12th, i want to show june 1st - june 30th

 

[soutener]

i found this in another forum, but being the n00b that i am, i still cant get anything to work right (my sql guy is on vacation, so its now my charge to figure it out)
i've already editied it with the right table/column names, but no dice, here is the code:

declare @FirstDayOfMonth, @FirstDayOfLastMonth

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 ivhsth
where IV_DATE >= @FirstDayOfLastMonth
and IV_DATE < @FirstDayOfMonth


___________

here is my error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@FirstDayOfMonth".
Msg 137, Level 15, State 1, Line 4
Must declare the scalar variable "@FirstDayOfLastMonth".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@FirstDayOfLastMonth".

 

[gmmastros]

You are not defining a data type for the variables.

declare @FirstDayOfMonth DateTime
declare @FirstDayOfLastMonth DateTime



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[soutener]

bah, it almost worked, i get last month and some of this month... i cant read the matrix yet, so can you hlep me figure it out


and thanks for your help

 

[soutener]

scratch the last one, i think i read wrong field

thanks again george

 

[gmmastros]

no problem. glad you got it working.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom