Grabbing data for last month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grabbing data for last month

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2012-08-14 : 13:03:33

How would I set an expression up to grab data from the previous calendar month when it's run?

I tried this for the first day of last month:

DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, "1/1/1900", Today())-2, "1/1/1900")

And this for the last day:

DateAdd(DateInterval.Day,-1*DatePart(DateInterval.Day,Today()),Today())

But it's giving me a syntax error. What is the proper way to do this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-14 : 13:11:21

dateadd(mm,datediff(mm,0,getdate()),0) will give the first day of the current month
so first day of last month is
dateadd(mm,datediff(mm,0,getdate()-1),0)
and the last day is
dateadd(mm,datediff(mm,0,getdate()),0)-1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 13:15:48

are you asking for t-sql or ssrs expression?

t-sql expression will be like


SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) AS StartofprevMonth,
DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)-1 AS EndOfPrevMonth

ssrs you will do like


=DateAdd(DateInterval.Month,DateDiff(DateInterval.Month, #1/1/1900#, Now())-1,#1/1/1900#)
=DateAdd(DateInterval.Day,-1,DateAdd(DateInterval.Month,DateDiff(DateInterval.Month, #1/1/1900#, Now()),#1/1/1900#))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2012-08-16 : 08:21:48

Thanks guys. This was initially going to be set up in the report unless I couldn't get that going, then I would have put it in the stored procedure. The expression for the report worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources