How to Get the Last Month Data and Month to Date Data

How to get the last month data and month to date data

Today including time info  : getdate()
Today without time info    : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
Tomorrow without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)
Beginning of current month : DATEADD(month, datediff(month, 0, getdate()), 0)
Beginning of last month    : DATEADD(month, datediff(month, 0, getdate())-1, 0)

so most likely

WHERE dateColumn >= DATEADD(month, datediff(month, 0, getdate())-1, 0)
  AND dateColumn <  DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)

SQL date scenario for getting previous month's data to-date on the 1st of month, then drop last month

WITH dates AS (SELECT * FROM VALUES ('2021-03-01'),
     ('2021-03-02'),
     ('2021-03-03'),
     ('2021-02-01'),
     ('2021-02-02') v(day))
SELECT day
    ,DATE_TRUNC('month',DATEADD('day',-1, day)) AS from_date
    ,DATEADD('month',1, from_date ) AS to_date
FROM dates
ORDER BY 1;

we get the results:

DAY         FROM_DATE                   TO_DATE
2021-02-01  2021-01-01 00:00:00.000     2021-02-01 00:00:00.000
2021-02-02  2021-02-01 00:00:00.000     2021-03-01 00:00:00.000
2021-03-01  2021-02-01 00:00:00.000     2021-03-01 00:00:00.000
2021-03-02  2021-03-01 00:00:00.000     2021-04-01 00:00:00.000
2021-03-03  2021-03-01 00:00:00.000     2021-04-01 00:00:00.000

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.

Calculate the date of the previous month and the date before previous month using current date (data flow, ADF)

You can use subMonths function, something like:

subMonths(toDate('2020-01-01'), 1)

subMonths(toDate('2020-01-01'), 2)

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.

---

Related Topics