• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

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
0
deanlee17
Asked:
deanlee17
  • 5
  • 5
  • 3
  • +1
1 Solution
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Anthony PerkinsCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now