asked on DB2: Help creating a query that must run bi-monthly.
- When the report runs on the 16th of the month, it should pull all data from the 1st to the 15th of that same month.
- When the report runs on the 1st of the following month, it should pull all data from the 16th of the previous month to the last day of the previous month.
I've found examples giving hints on how to accomplish this in T-SQL, but not necessarily in DB2. I know a lot of T-SQL date functions don't translate well. Can anyone assist with this piece of code? Thanks.
Please try this and see if the syntax is valid. DB2's kind of a loner on allowing this type of (easy) syntax, so I'm not super-familiar with it.
SELECT ...
FROM ...
WHERE
((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 16 AND
(column_date BETWEEN CURRENT DATE - 15 DAYS AND CURRENT DATE - 1 DAY)) OR
((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 1 AND
(column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY))
I run your version of the query and it runs with no syntax errors, but it returns 0 rows when I know it should have at least 10+.
Edit: I believe I see why. There's no logic in it to return anything if you run it for any days other than the 16th or the 1st.
»bp
»bp
For testing today, just change one of them to "= 4" rather than "= 16" or "= 1" :-).
So changing
(DAY(CURRENT DATE) = 1 AND column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY)
to
(DAY(CURRENT DATE) = 4 AND column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY)
will now select rows between 1/19/2020 and 2/3/2020. It will still validate that this approach works, just be prepared for the dates to be skewed a bit in the test...
»bp
@Scott, thanks for setting us on the right path. Much appreciated.
»bp
Could:
((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 16 AND
Just be:
(DAY(CURRENT DATE) = 16 AND
Maybe, I wasn't sure. Again, DB2 syntax is rather quirky at times.
Which specific version of DB2: Unix/Linux/AIX, Windows, Z/OS, iSeries, etc.? Sadly, even for something as simple as working with dates, there can be differences depending on the specific db engine version.