How to filter only the last day of each month?

Asked

Viewed 213 times

4

How can I filter a query bringing the result by the last day of each month, in SQL Server 2012? I tried the EOMONTH, I think due to the version.

SELECT E8_FILIAL,
       E8_AGENCIA, E8_CONTA, 
       E8_DTSALAT, 
       E8_SALATUA 
  FROM SE8010 AS SE8
 WHERE E8_CONTA IN ('84361','22700') 
   AND D_E_L_E_T_ <> '*' 
   AND YEAR(E8_DTSALAT) = 2018

I need to bring these fields filtering by day 31/01, 28(or 29)/02, 31/03, 30/04, etc.

the date field is the E8_DTSALAT

sql sql-server query script

  • Which database?

  • @Marcianomachado Sql Server

  • If I understand your doubt, you want to filter from the 31st until the 1st, is that it? In this case could use a order by E8_DTSALAT desc.

  • @Thiagoalessandro Encapsulating column in the WHERE clause with function is not a good practice as it makes the code non sargable. See article Construindo códigos T-SQL eficientes: Sargability in https://portosql.wordpress.com/2018/10/04/construindo-codigos-t-sql-eficientes-sargability/

2 answers

3


From SQL 2012 should be able to use EOMONTH:

SELECT  E8_FILIAL
    ,   E8_AGENCIA
    ,   E8_CONTA
    ,   E8_DTSALAT
    ,   E8_SALATUA 
FROM    SE8010 AS SE8
WHERE   E8_CONTA                    IN ('84361','22700') 
    AND D_E_L_E_T_                  <> '*' 
    AND YEAR(E8_DTSALAT)            = 2018 
    AND CAST(E8_DTSALAT AS DATE)    = EOMONTH(E8_DTSALAT)

But if the version does not allow it can do otherwise:

SELECT  E8_FILIAL
    ,   E8_AGENCIA
    ,   E8_CONTA
    ,   E8_DTSALAT
    ,   E8_SALATUA 
FROM    SE8010 AS SE8
WHERE   E8_CONTA                    IN ('84361','22700') 
    AND D_E_L_E_T_                  <> '*' 
    AND YEAR(E8_DTSALAT)            = 2018 
    AND CAST(E8_DTSALAT AS DATE)    = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, E8_DTSALAT) + 1, 0) - 1 AS DATE)

CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, E8_DTSALAT) + 1, 0) - 1 AS DATE)

The DATEADD allows adding a value, be it day, month, year, time, etc., to a certain date;
The attribute MONTH states that we want to add months to the date;
The DATEDIFF returns the difference between two dates, in which case returns the total of months between the minimum date and the date in the column E8_DTSALAT;
Finally the CAST allows you to convert the value to a date only (no time, minute, second, etc).

What this code does is basically remove 1 day from the first of the month following the E8_DTSALAT, thus obtaining the last day of the month.

  • was preparing the answer but gave no time =p that’s right!! (strange is that the EOMONTH is available from the 2012)

  • @João Martins worked, could you explain the last AND in condition Where, the option without eomonth, please? My knowledge doesn’t go far beyond the basics.

  • Right, I also saw the same thing! Something that may be happening is the SQL instance being a version prior to 2012 but the SSMS being the 2012 version!

  • @Thiagoalessandro edited reply to contain more information. Do not forget to give an UP and/or accept the answer if it was useful!

0

I need to bring these fields filtering by day 31/01, 28(or 29)/02, 31/03, 30/04, etc.
The date field is E8_DTSALAT

From what I understand, want the query return the information on 31/1/2018, 28/2/2018, ..., 31/12/2018. At first each month has always the same number of days, except in leap years, for the month of February.

Here is a suggestion, which informs the year; from it the last day of each month of that year is generated through CTE (common table Expression).

-- código #1
-- informe o ano
declare @Ano smallint;
set @Ano= 2018;

--
with 
Inicio_Meses as (
SELECT convert (date, ('1/'+cast(n as varchar(2))+'/'+cast(@Ano as char(4))), 103) as Data_Inicio
 from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) as Mes (n)
),
Fim_Meses as (
SELECT dateadd (day, -1, dateadd (month, +1, Data_Inicio)) as Data_Fim
  from Inicio_Meses
)
-- a consulta
SELECT E8_FILIAL, E8_AGENCIA, E8_CONTA, 
       E8_DTSALAT, E8_SALATUA 
  from SE8010 AS SE8
 where SE8.E8_CONTA in ('84361','22700') 
       and SE8.D_E_L_E_T_ <> '*' 
       and SE8.E8_DTSALAT in (SELECT Data_Fim from Fim_Meses);

CTE Fim_months generates the last day of each month, according to the reported year. For testing purposes, try:

-- código #2 v2
declare @Ano smallint;
set @Ano= 2020;

with 
Inicio_Meses as (
SELECT convert (date, ('1/'+cast(n as varchar(2))+'/'+cast(@Ano as char(4))), 103) as Data_Inicio
 from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) as Mes (n)
),
Fim_Meses as (
SELECT Data_Inicio, 
       dateadd (day, -1, dateadd (month, +1, Data_Inicio)) as Data_Fim
  from Inicio_Meses
)
SELECT * from Fim_Meses;

Browser other questions tagged sql sql-server query script

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