SQL Query Last Month

Question

Hello,,, anyone know how to make query to get data from last month and not for last 30 days?

Answer 1

Hi @Handian Sudianto

You can try this query.

create table #test(id int,date datetime);
insert into #test values
(1,'2023-01-06'),
(2,'2023-02-01'),
(3,'2023-02-12'),
(4,'2023-02-15'),
(5,'2023-03-07'),
(6,'2022-12-10'),
(7,'2022-12-20');
select * from #test 
where (month(date) = month(getdate()) - 1 and year(date) = year(getdate()))
or (year(date) = year(getdate()) - 1 and month(date) = 12 and month(getdate()) = 1);

Output:

It will find data for February 2023.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

CREATE TABLE #mydata (id      int NOT NULL PRIMARY KEY,
                      date    date NOT NULL,
                      comment nvarchar(100) NOT NULL)
INSERT #mydata (id, date, comment)
  VALUES(1, '20230131', 'This row is too old'),
        (2, '20230201', 'But this one makes the cut'),
        (3, '20230228', 'And so does this one.'),
        (4, '20230301', 'However, this one is too new')

-- SQL 2022, Azure SQL Database and Azure Managed Instance
SELECT id, date, comment
FROM   #mydata
WHERE  date >= dateadd(MONTH, -1, datetrunc(MONTH, sysdatetime()))
  AND  date <  datetrunc(MONTH, sysdatetime())

-- Older versions of SQL Server.
SELECT id, date, comment
FROM   #mydata
WHERE  date >= dateadd(MONTH, -1, convert(date, convert(char(6), sysdatetime(), 112) + '01'))
  AND  date <  convert(date, convert(char(6), sysdatetime(), 112) + '01')
go
DROP TABLE #mydata

Answer 3

If you are looking for a T-SQL select query to get data from last month

then you can use between dates to get the data else please elaborate your exact requirement

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN  '2022-02-01' AND '2022-02-28';

https://www.w3schools.com/sql/sql_between.asp

More example and syntax you can get from here

https://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server

Answer 4

I grad the query from an earlier answer:

Select * from yourtable

Where AC_DT > EOMONTH(DATEADD(month, -2, GETDATE()))

and AC_DT <= EOMONTH(DATEADD(month, -1, GETDATE()))