SQL Max and Min date for prev month

try

where date between
 dateadd(month, -1,
   dateadd(dd, -1 * datepart(dd, getdate()) + 1, getdate())
)
and
   dateadd(dd, -1 * datepart(dd, getdate()) , getdate())

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()))

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)

Select allOpen in new window

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.

momi_sabag:

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

Thanks,
Dean.

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

momi_sabag, ive found a problem, it doesnt bring back results for 1st Feb only 2nd feb onwards?!

Thanks,
Dean

what exactly did you execute?

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"

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)

Select allOpen 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)

Hi acperkins

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

Thanks,
Dean.

acperkins, that worked perfectly. Can you explain where why momi_sabag's method failed as opposed to yours?

>>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.