SQL get previous month (in January too)

Question

Hello I'm looking for simple way, how to get data from previous month. I get this code but it didn't work in January (result is 12 2021 and I need 12 2020)

select month(dateadd(month,-1,getdate())), year(getdate())

Answer 1

Presumably, you have some sort of date column.

In SQL Server, you can express this concept using datediff():

where datediff(month, datecol, getdate()) = 1

However, that is not "sargable", meaning that it prevents the use of indexes. So, I would instead recommend:

where datecol < datefromparts(year(getdate()), month(getdate()), 1) and
      datecol >= dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1))

If you simply want the first day of the previous month, you can use:

dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1))

Answer 2

Using the answer given here: How can I select the first day of a month in SQL?

SELECT dateadd(month,-1,DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as previousmonth;

output: 2020-12-01 00:00:00.000

Answer 3

I can provide next query using FORMAT function:

SELECT 
    -- get current day in previous month
    FORMAT(dateadd(month, -1, getdate()), 'yyyy-MM-dd') as current_previousmonth,
    -- get first of previous month
    FORMAT(dateadd(month, -1, getdate()), 'yyyy-MM-01') as first_previousmonth,
    -- previous month without date
    FORMAT(dateadd(month, -1, getdate()), 'yyyy-MM') as previousmonth;

test T-SQL here

Answer 4

Here you go!

select dateadd(mm,-1,eomonth(getdate())) as [Previous Month]

Result:

Previous Month
--------------
2020-12-31

You could also use CONVERT() or FORMAT() functions to format the date as you desire.

Answer 5

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