On 10-Oct-2017 07:57 -0600, dmmueller wrote:
I have a date field "payment date" that I need to compare to current
date, but only comparing the year and month.
Any payments received in the current month should be disregarded,
I know this is simple, and I'm over thinking it.
The apparent chosen solution
[
https://archive.midrange.com/midrange-l/201710/msg00215.html] and
several similar suggestions in that thread, each involved expression(s)
that would be performed on the row data in the file, to effect the
selection, thus a full table/data scan. The following approach uses an
effective constant to compare, without any expression for the
column-data to match data types; i.e. a key/index on the column/data
could be utilized to implement the query.
[Database->Reference->SQL reference->Built-in functions->Scalar
functions->TRUNC_TIMESTAMP](
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzscatruncts.htm)
Seemingly implied, that there are assuredly no future dates in the
data [in months or years], thus any days in the current month and those
in the future, are validly excluded from the result data:
WHERE payment_date < date( TRUNC_TIMESTAMP( current_date, 'MM' ) )
That presumably, and fairly succinctly, implements the logic Alan
referenced/posted in a reply
(
https://archive.midrange.com/midrange-l/201710/msg00198.html)
The use of the DATE scalar in the above is optional; i.e. the SQL
would implicitly cast the result of the expression using the CURRENT
DATE special register into the DATE data type to match the field named
PAYMENT_DATE.
[Database->Reference->SQL reference->Built-in functions->Scalar
functions->LAST_DAY](
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscalastday.htm)
In another situation, unlike any plausible "payment date", for which
results /outside the current month/ should include future dates in the
result data, then with a slight revision and the additional scalar
mentioned/linked above:
WHERE payment_date not between
date( TRUNC_TIMESTAMP( current_date, 'MM' ) )
and date( TRUNC_TIMESTAMP( LAST_DAY( current_date ), 'DD' ) )
Again, the DATE scalar would be optional; left for emphasis on intent.
midrange.com
