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?
It seems the shortest way would be to use
dataven between dateadd(day, -5, eomonth(getdate(), -1)) and dateadd(day, 10, eomonth(getdate(), 0))