GETDATE last month

Question

I am trying to list the latest site statistics. I have listed the last 30 days since;

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 which query to use in the last month. I tried:

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

Does not work.

Answer 1

Below you will find the beginning of the last month:

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

Then you will find the beginning of this month using the following, minus one.

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

When I have to work with dates in SQL Server, I often refer to Robyn Page SQL Server DATE / TIME Workbench . The Workbench (tutorial) is well laid out and contains almost everything I have ever needed when working with dates on SQL Server.

Answer 2

Dates are always happy to work in any programming language, SQL is not excluded.

To answer your question, to find all the 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 extend the best means of retrieving records over a specific time interval, use the dated file function, the dateadd function, and the condition between conditions 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 not return records because it checks if today is between 1900-01-01 00: 00: 00.000 and the last possible recorded date of the last month (last day and 23:59: 59.997 - SQL Server DATETIME columns have no more than 3 milliseconds).

The following code will return the record as the date we are looking for, a 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)) 

Where clause break:

 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, many dates can be set this way, 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 list above, you can define a range of any type.

Answer 3

How about this?

 select DATEADD(month, -1, GETDATE()) 

Answer 4

I would suggest using the first day of the last month and the first day of the current month for the operation and instead of using BETWEEN use> = and <. This is my personal opinion, but I believe that you will find that there are performance and support advantages for this approach.

Here sql. You will notice that I have included the last day of the last month in case you end up with a different approach.

Keep in mind that these dates are based on 12:00 on that day. In other words, getting values between 6/1/2009 and 6/30/2009 will not give you what you want, since all 6/30/2009 is excluded. If you use the first day of July (7/1/2009), you are covered.

Again, I recommend avoiding BETWEEN as shown below. Good 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 5

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

Answer 6

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

There may not be good performance, but you have an idea.

Answer 7

Try:

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

...

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

Answer 8

GET THE FIRST DAY OF THE LAST MONTH

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

LAST DAY OF THE LAST MONTH

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

Then search based on this range.