GETDATE last month

Question

I am trying to list last a website's statistics. I listed Last 30 days with;

CONVERT(VARCHAR(10), S.DATEENTERED, 101) BETWEEN CONVERT(VARCHAR(10), GETDATE()-30, 101) AND CONVERT(VARCHAR(10), GETDATE(), 101)

and this month with;

RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) = RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7)

but I have no idea what query to use for last month. I tried with;

RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) = RIGHT(CONVERT(VARCHAR(10), GETDATE()-1, 103), 7)

Did not work.

Answer 1

Try using the DATEADD function. You can add a -1 with the MONTH (mm) datepart and it should work. Here is a link

Answer 2

where year(S.DATEENTERED) = year(dateadd(mm, -1, getdate())) and month(S.DATEENTERED) = month(dateadd(mm, -1, getdate()))

Might not be good performance-wise but you've got the idea.

Answer 3

Try:

declare @lastm int
set @lastm = datepart(mm,getdate()) - 1

...

where datepart(mm,s.dateentered) = @lastm

Answer 4

GET FIRST DAY OF LAST MONTH

SELECT DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1,GETDATE())), '01/01/2000')

GET LAST DAY OF LAST MONTH

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,'01/01/2000',GETDATE()),'01/01/2000'))

Then search based on this range.

Answer 5

Dates are always a joy to work with in any programming language, SQL not excluded.

To answer your question to find all records that occurred last month

select S.DATEENTERED
      ,*
  from sometable S
 where S.DATEENTERED
       between dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())), 0)
           and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())) + 1, 0))
order by 1

To expand the best means for getting records within a certain time-frame is by utilizing the datediff function, dateadd function, and the between condition in the where clause.

select 'howdy'
      ,getdate()
 where getdate()
       between dateadd(mm, 0, 0)
           and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0))

The above code will result in no records returned because it is checking to see if today's date is between 1900-01-01 00:00:00.000 and the last possible recorded date of last month (the last day and 23:59:59.997 - SQL Server DATETIME columns have at most a 3 millisecond resolution).

The following code will return a record as the date we are searching for is one month ago.

select 'howdy'
      ,dateadd(mm, -1, getdate())
 where dateadd(mm, -1, getdate())
       between dateadd(mm, 0, 0)
           and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0))

A break down of the where clause:

WHERE getdate()  -- date to check
between dateadd(mm, 0, 0) -- begin date
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0)) -- end date

Finally, a variety of dates can be ascertained in this manner here is a pretty complete list:

select dateadd(mm, 0, 0) as BeginningOfTime
      ,dateadd(dd, datediff(dd, 0, getdate()), 0) as Today
      ,dateadd(wk, datediff(wk, 0, getdate()), 0) as ThisWeekStart
      ,dateadd(mm, datediff(mm, 0, getdate()), 0) as ThisMonthStart
      ,dateadd(qq, datediff(qq, 0, getdate()), 0) as ThisQuarterStart
      ,dateadd(yy, datediff(yy, 0, getdate()), 0) as ThisYearStart
      ,dateadd(dd, datediff(dd, 0, getdate()) + 1, 0) as Tomorrow
      ,dateadd(wk, datediff(wk, 0, getdate()) + 1, 0) as NextWeekStart
      ,dateadd(mm, datediff(mm, 0, getdate()) + 1, 0) as NextMonthStart
      ,dateadd(qq, datediff(qq, 0, getdate()) + 1, 0) as NextQuarterStart
      ,dateadd(yy, datediff(yy, 0, getdate()) + 1, 0) as NextYearStart
      ,dateadd(ms, -3, dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)) as TodayEnd
      ,dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0)) as ThisWeekEnd
      ,dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)) as ThisMonthEnd
      ,dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()) + 1, 0)) as ThisQuarterEnd
      ,dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)) as ThisYearEnd

Using the above list a range of any type can be determined.

Answer 6

I would suggest using the first day of last month and the first day of the current month for the operation and rather than using BETWEEN use >= and <. That's my personal opinion, but I believe you will find there are performance and maintainability benefits to this approach.

Here's the sql. You will notice I've included the last day of the last month value just in case you end up going with another approach.

Keep in mind, these dates are based off of 12:00AM that day. In other words, getting values between 6/1/2009 and 6/30/2009 won't get you what you want as all of 6/30/2009 is excluded. If you use the first day of July (7/1/2009) you are covered.

Again, I recommend avoiding BETWEEN all together as shown below. Best of luck.

Declare @LastMonthFirstDay datetime
Declare @LastMonthLastDay datetime
Declare @ThisMonthFirstDay datetime

Set @LastMonthFirstDay =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0);
Set @ThisMonthFirstDay = DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0);
Set @LastMonthLastDay = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0));

Select * From Table
Where DateEntered >= @LastMonthFirstDay 
And DateEntered < @ThisMonthFirstDay;

Answer 7

The following will find you the start of the last month:

-- Start of last month 
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)

You would then find the start of this month, using the following, minus one.

-- Start of the month 
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime) 

I use this tutorial alot when working with dates in SQL, it is well laid out and contains just about everything I have ever needed when working with dates. http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/