× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.