AW: Date failing issue in SQL

Just as an aside for using scalar or user defined functions within the WHERE Conditions.
Using a function on the left side of the comparison operator prevents the query optimizer from using an index built over the appropriate column.
The only situation where the optimizer could use an index is if there is an derived index with a key column written in exactly the same way as the where condition.

i.e. WHERE Year(SalesDate) = 2016 (prevents the optimizer from using an index built over the SalesDate Column.
An index with the following definition might be used by the optimizer: CREATE INDEX MYSCHEMA.MYINDEX ON MYSCHEMA.SALES (Year(SALESDATE) AS SALESYEAR ASC ....)
Using WHERE SALESDATE between '2016-01-01' and '2016-12-31' would be the better solution in this situation.

If you want to compare a numeric date with the current date in your program.
It would be far better to convert the current date into a numeric representation outside the SQL statement, store it in a Host variable and use this host variable within your SELECT-Statement.
Alternatively a global variable could be used to hold the numeric current date.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Rob Berendt
Gesendet: Thursday, 13.10 2016 20:04
An: Midrange Systems Technical Discussion
Betreff: Re: Date failing issue in SQL

To both,

I KNOW that iDate can convert from nondate to date, AND date to nondate.
But I am going back to the performance suggestion of NOT converting the value from each row of your table, but instead, just convert the constant.
If you feel you just HAVE to use iDate then use it in this fashion:
table.column = idate(current date, makeitanumber) or however the syntax goes Do not use it in this fashion idate(table.column, makeitadate) = current date

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Charles Wilt <charles.wilt@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/13/2016 01:50 PM
Subject: Re: Date failing issue in SQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



The idate package includes both the iDate() function to convert from
character/numeric to date and the ConvertToiDate() function to convert
from
date to numeric.

Charles

On Thu, Oct 13, 2016 at 1:22 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

While I have iDate I would not use it to convert the file's numeric date
but, rather, to convert the (current date) into the format desired.

Again,

for performance reasons, and, to avoid converting invalid dates.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Campin <alan0307d@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/13/2016 01:12 PM
Subject: Re: Date failing issue in SQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Why not simply use iDate at www.think400.dk/downloads.htm?

Select iDate(anAS400Date) From X

I assume what you are dealing with is an alpha field with a date. iDate
will handle that correctly.

On Thu, Oct 13, 2016 at 9:52 AM, CRPence <crpbottle@xxxxxxxxx> wrote:

On 13-Oct-2016 08:53 -0500, Hoteltravelfundotcom wrote:

On 13-Oct-2016 08:20 -0500, Hoteltravelfundotcom wrote:

I have this SQL query that has been halting since 9/28/16 during the
crystal report run, it was working fine for over 2 years. The error
getting is this:

'SQL0181 Value in Date, Time, or Timestamp not valid Database
vendor code -181'

sqlcode=-181 msg SQL0181

I checked against this date IATRND, via a query/400 and see nothing
funky.

And that column name was derived from wheree already? If mentioned

in

the above message, that was omitted. And now having been mentioned,

what

are the attributes; i.e. what was the DDL to define that column? When
describing an issue with an expression involving a column, the actual
data-type and precision [and scale and for date/time representations,

then

also the /format/ of the data the column represents] are important to
reveal to avoid a reader having to guess or infer -- thus likely

prolonging

the agony of extracting the details from the OP that easily could have

been

there initially.

Is there a way to show me which particular date and order # is the
problem?

Yes. Ask for a revised version of that expression [shown in the

WHERE

clause on an equal predicate shown just below in the quoted text that

was

not snipped] in the select-list, by having removed the DATE casting

scalar

*plus* either removing that predicate comparing with current_date or
changing the expression to remove the DATE casting scalar in

conjunction

with replacing the current_date with a string literal such as
'2016-10-13'. Then a report for the query will show the date-strings
instead of trying to generate DATE data-typed values.

However depending on the amount of data, that may not be very

helpful,

because visual inspection can be tedious. So better to wrap the

expression

in a scalar with a User Defined Function (UDF) with a function implied

by

the name, of IS_DATE or ISDATE; a UDF providing the ability to both

present

an indication that the data is not valid for producing a DATE and the
ability to add a predicate on the WHERE clause to select *only* those
values which can not produce a valid DATE. The logic for testing

valid

dates is not as simple as with using a UDF, within an actual query; I

have

posted past examples however, of how to exhaustively validate all of

the

components of a date-like value.

[…]
and date(varchar( integer(T01.IATRND/10000)) || '-' ||
varchar( integer(T01.IATRND/100)
- integer(T01.IATRND/10000) * 100 ) || '-' ||
varchar(mod(T01.IATRND, 100))
) = current date

Why such a complicated expression for what appears to be such a

simple

task? The order of the date-components [i.e. YYYY, MM, DD] that is

implied

by the arithmetic expression shown, is YYYY, MM, and DD; i.e. from the
expression can be inferred, that the column IATRND is [of at least] an
8-digit number in the format YYYYMMDD.

As Birgitta showed, there is a much simpler expression to cast such

a

column to a TIMESTAMP [which can compare to a DATE such as the CURRENT

DATE

special register]; revised here, to be /safer/ per ensuring exactly an
8-digit value cast into character using the DIGITS casting scalar, and

done

so, per lack of the DDL offered in the OP:

Date( Digits( DEC(T01.IATRND, 8) ) concat '000000'))

And as Rob suggests, although performing such a casting on the

column

can be functional, that does not necessarily imply also that the

request

is

practical or favorable. As he alludes, the better option is to cast

the

CURRENT_DATE into whatever matches the DDL [and date-like formatting]

for

the column IATRND; e.g. assuming the DDL was `IATRND INTEGER`, then

the

predicate in the WHERE-clause could be better expressed with one of

the

following [though NB, according to the comments]:

T01.IATRND = INT( current date ) /* for use on release and

maintenance

levels for which date casting to numeric [implicitly, in the format
YYYYMMDD] is supported */

T01.IATRND = INT( REPLACE( CHAR(current_date,ISO), '-', '' ) )

Some of the rows are this:

TRANSACTION DATE: IATRND = 0

is this the issue?

Easy. Anywhere in an expression where IATRND is referenced, just
replace any *one occurrence* with: NULLIF(IATRND, 0)

For example, code `integer(NULLIF(T01.IATRND, 0)/100)` in place of

what

is shown coded currently as `integer(T01.IATRND/100)'

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.