How to search 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. someone could 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 1

In the SQL Server 2008 you can do something like this:

SELECT
    SUM(DIFERENÇA) AS Mesanterior
FROM
    TOTALIZADOR
WHERE
    NID = 252 AND
    DATAHORA BETWEEN
        DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))
    AND
        DATEADD(s,1,DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())-1,0)));

Retrieves the date of the first day of last month:

SELECT DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0));

Retrieves the date from the last day of last month:

SELECT DATEADD(s,1,DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())-1,0)));

If your server is equal to or higher than SQL Server 2012, you can use the function facility EOMONTH() to obtain the last day of the month:

SELECT
    SUM(DIFERENÇA) AS Mesanterior
FROM
    TOTALIZADOR
WHERE
    NID = 252 AND
    DATAHORA BETWEEN
        DATEADD( DAY, 1, EOMONTH(GETDATE(), -2))
    AND
        EOMONTH( GETDATE(), -1 );

Retrieves the date of the first day of last month:

SELECT DATEADD( DAY, 1, EOMONTH(GETDATE(), -2));

Retrieves the date from the last day of last month:

SELECT EOMONTH( GETDATE(), -1 );

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql

Answer 2

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 numerical range (signed integer) added to the specified date part.

---

DATEPART

Returns an integer representing part of the specified date.

---

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.

---

Observing: This way it will be verified if the month and year are equivalent, so it will compare the days 31 months with more days (January, March, May, July, August, October and December) and 28/29 february.