SQL Max and Min date for prev month

Hi experts.

Not sure is this is possible but im creating a view to pull data for the previous month. I need the WHERE clause to have the Min date of the first day of perv month and the Max date to be last day of the previous month.

Example:

Current Date:  2012-03-12

WHERE Date Between '2012-02-01 AND '2012-02-29'

Thanks,
Dean
deanlee17Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
No, but with SQL Server 2008 you can use the new date data type and just do:

WHERE [Date] BETWEEN DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, CAST(GETDATE() AS date))), DATEADD(MONTH, -1,CAST(GETDATE() AS date)))
      AND DATEADD(DAY, -DAY(CAST(GETDATE() AS date)), CAST(GETDATE() AS date))
0
 
momi_sabagCommented:
try

where date between
 dateadd(month, -1,
   dateadd(dd, -1 * datepart(dd, getdate()) + 1, getdate())
)
and
   dateadd(dd, -1 * datepart(dd, getdate()) , getdate())
0
 
YZlatCommented:
WHERE MyDate BETWEEN DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,GETDATE()))
AND DATEADD(dd, -DAY(DATEADD(m,1,GETDATE())), DATEADD(m,0,GETDATE()))
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Anthony PerkinsCommented:
If you do not want the time in the condition and you are not using SQL Server 2008 then consider this small variation (no points please):

DECLARE @Today smalldatetime
SET @Today = DATEADD(day, 0, DATEDIFF(DAY, 0, GETDATE()))

...

WHERE Date BETWEN DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, @Today)), DATEADD(MONTH, -1, @Today)) AND DATEADD(DAY, -DAY(@Today), @Today)

Open in new window

0
 
deanlee17Author Commented:
acperkins: Could you explain exactly how... these lines work....

= DATEADD(day, 0, DATEDIFF(DAY, 0, GETDATE()))

BETWEN DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, @Today)), DATEADD(MONTH, -1, @Today)) AND DATEADD(DAY, -DAY(@Today), @Today)

Thanks.
0
 
deanlee17Author Commented:
momi_sabag:

This worked perfectly. Can you explain exactly how is works?

Thanks,
Dean.
0
 
momi_sabagCommented:
sure
dateadd(dd, -1 * datepart(dd, getdate()) + 1,  -- this returns the first day of the current month by subtracting the number of days that have passed since the month started

wrapping it with
 dateadd(month, -1,
 getdate())
)
will return the first day of last month

and
   dateadd(dd, -1 * datepart(dd, getdate()) , getdate()) -- this returns the last day of the previous month
0
 
deanlee17Author Commented:
momi_sabag, ive found a problem, it doesnt bring back results for 1st Feb only 2nd feb onwards?!

Thanks,
Dean
0
 
momi_sabagCommented:
what exactly did you execute?
0
 
Anthony PerkinsCommented:
it doesnt bring back results for 1st Feb only 2nd feb onwards?!
Now hopefully you see what I meant by "If you do not want the time in the condition"
0
 
Anthony PerkinsCommented:
Could you explain exactly how... these lines work....
See if this helps:
DECLARE @Today smalldatetime
SET @Today = DATEADD(day, 0, DATEDIFF(DAY, 0, GETDATE()))

SELECT	DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, @Today)), DATEADD(MONTH, -1, @Today)), 
	DATEADD(DAY, -DAY(@Today), @Today)

Open in new window


Incidentally, there was a typo in the solution i posted earlier this:
WHERE Date BETWEN DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, @Today)), DATEADD(MONTH, -1, @Today)) AND DATEADD(DAY, -DAY(@Today), @Today)

Should have read:
WHERE Date BETWEEN DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, @Today)), DATEADD(MONTH, -1, @Today)) AND DATEADD(DAY, -DAY(@Today), @Today)
0
 
deanlee17Author Commented:
Hi acperkins

Thanks for the reply, but I am using SQL Server 2008, will this cause a problem?

Thanks,
Dean.
0
 
deanlee17Author Commented:
acperkins, that worked perfectly. Can you explain where why momi_sabag's method failed as opposed to yours?
0
 
Anthony PerkinsCommented:
>>Can you explain where why momi_sabag's method failed as opposed to yours?<<
As I mentioned previously, the only difference between the two is the time.  See for yourself:
SELECT dateadd(month, -1,   dateadd(dd, -1 * datepart(dd, getdate()) + 1, getdate())),
   dateadd(dd, -1 * datepart(dd, getdate()) , getdate())
   
SELECT DATEADD(day, 1 - DAY(DATEADD(MONTH, -1, CAST(GETDATE() AS date))), DATEADD(MONTH, -1,CAST(GETDATE() AS date))),
       DATEADD(DAY, -DAY(CAST(GETDATE() AS date)), CAST(GETDATE() AS date))

That tell me that your date column includes a time.
0
All Courses

From novice to tech pro — start learning today.