CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Shahsra
Shahsra

Reputation: 1009

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

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

Upvotes: 11

Views: 59446

Answers (4)

eupton
eupton

Reputation: 221

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.

Upvotes: 1

Russell Steen
Russell Steen

Reputation: 6612

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 )

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

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)

Upvotes: 19

PaulF
PaulF

Reputation: 1183

Very helpful page

declare @d datetime = '2011-04-03';

declare @startDate datetime;
select @startDate =
   CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,@d),113),8) AS datetime);
select @startDate;

Upvotes: 0

Related Questions