CODEWITHSUNDEEP
sqlsql-serverdategetdate
sql-rookie
sql-rookie

Reputation: 23

SQL get previous month (in January too)

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())

Upvotes: 2

Views: 14983

Answers (6)

Yogesh Thapliyal
Yogesh Thapliyal

Reputation: 71

Try SELECT FORMAT(DATEADD(month, -1, GETDATE()),'MM/yyyy');

It will give you previous month and the year. If you are comparing to a date column in a existing table, then you need the date part too as you want to know December of which year was the previous month. But if you don't want the year, then just adjust the 'MM/yyyy' part to suit your purpose.

Upvotes: 0

Arkan I. Salman
Arkan I. Salman

Reputation: 60

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

Upvotes: 1

Poseidon
Poseidon

Reputation: 101

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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))

Upvotes: 3

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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

Upvotes: 0

Luuk
Luuk

Reputation: 14958

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

Upvotes: 0

Related Questions