1

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())
sql-rookie
  • 13
  • 1
  • 3

6 Answers6

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))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Luuk
  • 12,245
  • 5
  • 22
  • 33
0

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

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
0

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.

Poseidon
  • 93
  • 9
0

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

Arkan I. Salman
  • 50
  • 2
  • 7
0

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.

Yogesh Thapliyal
  • 69
  • 7