How to get the last month data and month to date data

Question

Need help in writing the query to get the last month data as well as month to date data.

If today's date is Mar 23 2011, I need to retrieve the data from last month and the data till todays date(means Mar 23 2011).

If date is Apr 3 2011, data should consists of March month data and the data till Apr 3rd 2011.

Thanks,

Shahsra

Solution 1

Today including time info  : getdate()
Today without time info    : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
Tomorrow without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)
Beginning of current month : DATEADD(month, datediff(month, 0, getdate()), 0)
Beginning of last month    : DATEADD(month, datediff(month, 0, getdate())-1, 0)

so most likely

WHERE dateColumn >= DATEADD(month, datediff(month, 0, getdate())-1, 0)
  AND dateColumn <  DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)

Solution 2

Step back one month, subtract the number of days to the current date, and add one day.

WHERE  
  DateField <= GetDate() AND
  DateField >= DateAdd(
      mm, 
      -1, 
      DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate())
  )

To remove the time quickly, you can use this Cast( Floor( Cast( GETDATE() AS FLOAT ) ) AS DATETIME )

So the second part would be (without time)

DateField >= Cast( Floor( Cast( (DateAdd(
          mm, 
          -1, 
          DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate())
      )) AS FLOAT ) ) AS DATETIME )

Solution 3

Select Column1, Column2 From Table1
Where DateColumn <= GetDate() AND 
DateColumn >= DATEADD(dd, - (DAY(DATEADD(mm, 1, GetDate())) - 1), DATEADD(mm, - 1, GetDate()))

Edit: +1 to Russel Steen. I was posting mine before I knew he had posted.

Comments

[RichardTheKiwi]

inexact - the 2nd one gives "2011-02-01 10:55:49.160"

[Russell Steen]

Nice, I like the datediff solution.

[Shahsra]

Thanks for giving me the link. Really its very useful.