Re: Date failing issue in SQL

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.