I have a complex (complex because it gave me lots of work to complete!) and it returns results that fulfill the query till the day 30 of the current month. Here is the code: I have a job that runs this query on the third monday of every month. What i need is that the query returns values not only

specific day of next month on a sql query

I have a complex (complex because it gave me lots of work to complete!) and it returns results that fulfill the query till the day 30 of the current month.

Here is the code:

SELECT 
    CONCAT(' ', CONVERT(date, fc.dataven), ' '), 
    fc.adoc, fc.nome, 
    CONCAT(CONVERT(money, fc.ecred), '  €')  
FROM
    fc, fl
WHERE
    dataven BETWEEN DATEFROMPARTS(IIF(MONTH(GETDATE()) = 1, YEAR(GETDATE())-1, YEAR(GETDATE())),
                                  IIF(MONTH(GETDATE()) = 1, 12, MONTH(GETDATE()) - 1), 30) 
                AND CAST(DATEADD(day,-day(GETDATE()),DATEADD(month, 1,GETDATE())) as DATE)

    AND fc.adoc NOT LIKE '' 
    AND fc.cmdesc LIKE 'v%'  
    AND fc.evalpo = '0' 
    AND (fc.cmdesc LIKE '%factura%' OR fc.cmdesc LIKE '%cred%')
    AND fc.nome = fl.nome 
    AND fl.pais LIKE 1
GROUP BY
    fc.adoc, fc.dataven, fc.nome, fc.ecred
ORDER BY 
    fc.nome

I have a job that runs this query on the third monday of every month.

What i need is that the query returns values not only till the 30th day of the current month but till the 10th day of the next month.

I’m a bit lost, can anyone give me a hint please?

Answer

It seems the shortest way would be to use EOMONTH function

    dataven between dateadd(day, -5, eomonth(getdate(), -1))
                and dateadd(day, 10, eomonth(getdate(), 0))

Advertisement

Answer