How to get the records of last month using sql query in SQL server?


Hello, I need to check my last month records in SQL server management studio, and I would like to know how can I get record of only last month from a table using SQL query, I have DateCreated column to check when the row was added in the table.

For example, as It's may,2018 and I would like to run a Query to get April,2018 records using sql server query.

If possible, I would also like to know, how to get last 30 days(not month) records in SQL server query.

Currently, I am using SQL server 2012.


Asked by:- neena
1
: 10825 At:- 5/9/2018 2:41:18 PM
SQL SQL server tsql get last month records







2 Answers
profileImage Answered by:- jaya

To get the last month records using SQL server, you can use the query below, It will give output of last month(for example if the month is May-2018, it will give April-2018 data)

SELECT * 
FROM TableName
WHERE DATEPART(m, DateCreated) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, DateCreated) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

In the above query, TableName is the table from which you want to fetch data while DateCreated is the column in which you have saved data as datetime

Now, to get Last 30 days records use the SQL server query as below

SELECT * FROM  TableName
WHERE  DateCreated >= DATEADD(day,-30, getdate()) 
and    DateCreated <= getdate()

If you have DateTime Column saved as a string first convert the string into datetime, your above query can be converted as below

SELECT * FROM  TableName 
WHERE  CONVERT(datetime, DateCreated) >= DATEADD(day,-30, getdate()) 
and    CONVERT(datetime, DateCreated) <= getdate()

So, any of the above query, depending upon your needs will work.

3
At:- 5/10/2018 8:37:30 AM Updated at:- 10/1/2022 6:21:10 AM
great answer, thanks for your help 0
By : neena - at :- 5/13/2018 7:09:02 PM


profileImage Answered by:- vikas_jk

You can also try this solution to get last month data in SQL

SELECT * FROM TableName
WHERE 
YEAR(DateColumn) = DATEPART(yyyy, DATEADD(m, -1, getdate()))
AND 
MONTH(DateColumn) = DATEPART(m, DATEADD(m, -1, getdate()))

This will return you last month results only.

0
At:- 10/1/2022 12:30:33 PM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use