Records only for the last month 1

[dwight1]

I have a table that has a datefield and acctType. I am currently able to query the number of accounts opened for the current month as follows.

DATENAME(month, DateOpend) and in the criteria field = DATENAME(month, GETDATE()). This would give me the accounts for the current month.

How do i get the number of accounts opened for the last month. This is the closest i could get
= DATENAME(month, GETDATE()-30) But then this is not good as we may have months less than or greater than 30.

Any advise

Dwight

 

[cLFlaVA]

you want dateadd.

... = datename( month, dateadd( 'm', -1, getdate() ) )

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[vongrunt]

Find beginning of month. That becomes end of interval (semi-inclusive).
Subtract one month. That becomes beginning of interval (inclusive).

Then run simple WHERE query:

declare @d2 datetime; set @d2 = dateadd(mm, datediff(mm, 0, getdate()), 0)
declare @d1 datetime; set @d1 = dateadd(mm, -1, @d2)

select *
from blah
where dateColumn >= @d1 and dateColumn < @d2

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dwight1]

ClFlava,

I tried to use the above expression it complains that

"ADO error:Invalid parameter 1 specified for dateadd"

Dwight

 

[cLFlaVA]

Sorry Dwight,

should be:

datename( month, dateadd( m, -1, getdate() ) )

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[dwight1]

You are great. Thank you for making my day! Thanks Vongrunt for your help.

You guys rock.

Dwight