Previous Month Function

Question

Post reply

Previous Month Function

Marv_SQL

SSCommitted

Points: 1612
More actions
November 24, 2010 at 12:49 pm

#231596

Hello,
Is there any commands or datediff functions that will always return the results for a prior month?
Let say I am running a query that has date filters between '9/1/2010' and '9/30/2010', can I do a date diff to also give me the results for the last full month? ('8/1/2010' and '8/31/2010')?
Thanks :hehe:
December 7, 2010 at 7:33 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

DateDiff(month, -1, '20100901')

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Shanalise Hall of Fame Points: 3188 More actions · Answer 2

You can use DATEADD as below

declare @myDay datetime

set @myDay = dateadd(month, -1, getdate())

selectgetdate() 'today',

@myDay 'Lastmonth'

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

If I understand you correctly, you always want the start and end dates from the prior month, regardless of the starting and ending date that you provide. So even though February ends on the 28th, you'd still want to see all 31 days of January.

declare @startdate date = '2/1/2010'

declare @enddate date = '2/28/2010'

declare @priorStart date = dateadd(month,(DATEDIFF(month,0,@startdate)-1),0) -- first day of month prior to @startdate

declare @priorEnd date = dateadd(day,-1,dateadd(month,(DATEDIFF(month,0,@startdate)),0)) -- last day of prior month

;with dates as (select top 1000 cast(dateadd(day,row_number() over (order by id),'1/1/2009') as date) as testDate

from syscolumns

)

select testdate, case when testDate between @startdate and @enddate then 'CURRENT' else 'Previous' end as _month

from dates

where testDate between @startdate and @enddate

or testDate between @priorStart and @priorEnd

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

The Dixie Flatline (12/6/2010)
If I understand you correctly, you always want the start and end dates from the prior month, regardless of the starting and ending date that you provide. So even though February ends on the 28th, you'd still want to see all 31 days of January.

Ya beat me to it, Bob! I've seen the "enddate" mistake several times and glad someone picked up on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Jeff Moden (12/6/2010)
The Dixie Flatline (12/6/2010)
If I understand you correctly, you always want the start and end dates from the prior month, regardless of the starting and ending date that you provide. So even though February ends on the 28th, you'd still want to see all 31 days of January.
Ya beat me to it, Bob! I've seen the "enddate" mistake several times and glad someone picked up on it.

Even a blind squirrel finds some nuts, Jeff. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

The Dixie Flatline (12/7/2010)
Jeff Moden (12/6/2010)
The Dixie Flatline (12/6/2010)
If I understand you correctly, you always want the start and end dates from the prior month, regardless of the starting and ending date that you provide. So even though February ends on the 28th, you'd still want to see all 31 days of January.
Ya beat me to it, Bob! I've seen the "enddate" mistake several times and glad someone picked up on it.
Even a blind squirrel finds some nuts, Jeff. 😉

How did I get involved in... oh, wait... wrong nuts. Whoops... :w00t:

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Nyuck, nyuck, nyuck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills