GETDATE last month
sql, sql-server, sql-server-2005
Solution
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)
When I have to work with dates in SQL Server I often reference Robyn Page's SQL Server DATE/TIME Workbench. The workbench (tutorial) is well laid out and contains just about everything I have ever needed when working with dates on SQL Server.
Problem
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.