sql – How do I get records from the previous month?

Question:

I need to get the data from the 1st to the 30th of the previous month, I'm using this syntax. can anybody help me?

SELECT SUM(DIFERENÇA)as Mesanterior
  FROM TOTALIZADOR
 WHERE NID = 252
   AND DATAHORA BETWEEN DATEADD(MM,-1,DATEADD(DD,-DAY(GETDATE())+1,GETDATE()))
   AND DATEADD(DD,-DAY(GETDATE()),GETDATE())

Answer:

You can use the following syntax in your WHERE :

SELECT SUM(DIFERENÇA) AS mesanterior
  FROM TOTALIZADOR
 WHERE NID = 252 
   AND DATEPART(MONTH, datahora) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))
   AND DATEPART(YEAR, datahora) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()));

DATEADD

Returns a specified date with the specified numeric range (signed integer) added to the specified date part.


DATEPART

Returns an integer representing the specified date part.


In detail:

  • DATEPART(MONTH, datahora) will get the month of registration;
  • DATEPART(MONTH, DATEADD(MONTH, -1, getdate())) will get the previous month for comparison;
  • DATEPART(YEAR, datahora) will get the year of registration;
  • DATEPART(YEAR, DATEADD(MONTH, -1, getdate())) will get the year of the previous month for comparison;
  • This way, it will be compared if the record has the month 06/2017 in the case of the example.

Note: This way it will be checked if the month and year are equivalent, therefore it will compare the 31st of the months with more days (January, March, May, July, August, October and December) and February 28th / 29th .

Scroll to Top