Get the Records of Last Month in SQL Server

Get the records of last month in SQL server

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

You need to check the month and year.

Get data from last month in SQL Server

The code I figured out is not pretty but it works. It first adds extra column with the month number to the SELECT portion of my code:

Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,'1971/12/31'),106))  As Month

And than is used for WHERE statement:

Month(CONVERT (VARCHAR(11),DATEADD(day,wo_header.closing_date,'1971/12/31'),106)) = month(getdate())-1

So for anyone like me working in SQL report kind-of environment it should work.

Select all where date in Last month sql

Edit

if you mean last month from today. or previous month from a specific date then you need to do something like this

SELECT DATEPART(MONTH, DATEADD(MONTH, -1, [Date]))

Or to get records from previous month of the year you can do something like this

SELECT * FROM Table
WHERE  MONTH(Date) =  DATEPART(MONTH, DATEADD(MONTH, -1, [Date]))
AND YEAR(Date) =    DATEPART(YEAR, DATEADD(MONTH, -1, [Date]))    --<-- or pass year for which year you are checking 

To make your aquery SARGable (Suggested by t-clausen.dk)

select * from table 
where date >=dateadd(m, datediff(m, 0, current_timestamp)-1, 0) 
and date < dateadd(m, datediff(m, 0, current_timestamp)-1, 0)

Read here more about sargable Queries when working with date/datetime datatypes.

SQL get previous month (in January too)

Try This

select CASE WHEN month(getdate())>1 THEN  month(getdate())-1   ELSE 12   END ,

CASE WHEN month(getdate())>1 THEN YEAR (getdate()) ELSE YEAR (getdate()) -1 END

SQL Server query to get the data from previous month if the column value is not present in next month

This answers the original version of the question.

Assuming that month is stored as a date with the first first day of the month, then a simple method uses recursive CTEs:

with cte as (
      select restaurant, num_items, month,
             dateadd(month, -1,
                     coalesce(lead(month) over (partition by restaurant order by month),
                              max(month) over ()
                             )
                    ) as end_month
      from t
      union all
      select restaurant, num_items, dateadd(month, 1, month), end_month
      from cte
      where month < end_month
     )
select *
from cte
order by restaurant, month;

Here is a db<>fiddle.

Get records of last 2 months (current year), and last month(last year)

You can try the logic as below-

SELECT * 
FROM your_table
WHERE 
(
    YEAR(paidDate) = YEAR(DATEADD(MM,-1, getdate())) 
    AND 
    MONTH(paidDate) =  MONTH(DATEADD(MM,-1, getdate()))
)
OR
(
    YEAR(paidDate) = YEAR(DATEADD(MM,-2, getdate())) 
    AND 
    MONTH(paidDate) =  MONTH(DATEADD(MM,-2, getdate()))
)
OR
(
    YEAR(paidDate) = YEAR(DATEADD(MM,-13, getdate())) 
    AND 
    MONTH(paidDate) =  MONTH(DATEADD(MM,-13, getdate()))
)

get all record of last month with respect to current month in sql server

You need to look at the first of the current and previous months and set your range to that.

select 
    p.payment_ID, 
    b.amount_To_Pay 
FROM 
    bill_tbl AS b 
    INNER JOIN payment_tbl AS p ON 
    b.payment_id = p.payment_ID 
WHERE
    (b.bill_status = 'Not Paid')
    and b.bill_IssueDate < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
    and b.bill_IssueDate >= DATEADD(month, DATEDIFF(month, 0, dateadd(month,-1,getdate())), 0)

---

Related Topics