How to search records from the previous month?

Asked

Viewed 4,957 times

3

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())
sql database sql-server
  • 1

    Possible duplicate of Select from previous months

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

2 answers

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

  • EOMONTH is not a recognized function

  • Check out my Sqlfiddle test: http://sqlfiddle.com/#! 6/9eecb7db59d16c80417c72d1e1f4fbf1/170/0

  • 1

    EOMONTH only applies to SQL Server 2012 or higher

  • @Ronaldosantos what is the version of your server ?

  • @Lacobus the solution you gave to SQL Server 2008 is identical to the one I suggested. It’s not very cool to copy an answer.

  • 1

    @Sorack I think you’re mistaken. Look well! I didn’t use DATEPART in my solution.

Show 1 more comment

1

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.

  • eu fiz assim " Select SUM(DIFERENÇA)as Mesanterior from TOTALIZADOR WHERE NID = 252 and DATAHORA between DateAdd(mm, DateDiff(mm,0,GetDate()) - 1, 0) AND DateAdd(mm, DateDiff(mm,0,GetDate()), -1) ";

  • @Ronaldosantos do not understand why the use of DATEDIFF. If you used the answer, mark it as chosen so that someone with similar doubt can benefit as well.

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.