We help IT Professionals succeed at work.

DB2: Help creating a query that must run bi-monthly.

Steven Ruiz
Steven Ruiz asked
on
I am writing a DB2 query that will be run bi-monthly - once on the 16th of the month and once on the 1st of the following month:

  • 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.
Comment
Watch Question

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

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.


Steven RuizApplication Analyst

Author

Commented:
Scott, I'm using IBM Data Studio 4.1.3 for an AIX DB.  If you need any more versioning info after that, let me know.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

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))



Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
@Scott,

Could:

((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 16 AND

Just be:

(DAY(CURRENT DATE) = 16 AND

So it becomes something like:

SELECT ...
FROM ...
WHERE
    (DAY(CURRENT DATE) = 16 AND column_date BETWEEN CURRENT DATE - 15 DAYS AND CURRENT DATE - 1 DAY) OR
    (DAY(CURRENT DATE) = 1 AND column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY)

Open in new window



»bp
Steven RuizApplication Analyst

Author

Commented:
Thanks Scott.  I needed a quick clarification - is "column_date" going to be the column that I want to specify the dates for?  If so, that column is a timestamp.  Will I need to convert it to a date?
Steven RuizApplication Analyst

Author

Commented:
Bill,

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.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Right, Scott assumed based on your original post that the code would only run on the 1st and the 16th of the month.


»bp
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
For testing you may have to replace "CURRENT DATE" with a date variable that you set to say the 1st or 16th of a month for testing...


»bp
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

For testing today, just change one of them to "= 4" rather than "= 16" or "= 1" :-).

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Just keep in mind that will change the selected data slightly since the calcs assumed 1 and 16 as the run days.

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
Steven RuizApplication Analyst

Author

Commented:
@Bill, your original suggestion worked like a charm.  Thanks.

@Scott, thanks for setting us on the right path.  Much appreciated.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome, glad we could help.


»bp
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

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.