AW: Date failing issue in SQL

in the place of varchar, ( date(varchar(integer(T01.IATRND/10000)) ||

'-'||) use DIGITS. varchar will drop leading zeroes, a common cause of this
issue.
But NOT for an integer field, it will return 10 digits (instead of 4)

Assumed IATRND is a 8 Digit numeric field containing a numeric date with the
format of YYYYMMDD, I'd convert it into a date as follows:
Date(Digits(T01.IATRND) concat '000000'))

Could it be there are several rows with a 0 date (which is indeed a invalid
date representation)


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 a4g
atl
Gesendet: Thursday, 13.10 2016 15:29
An: Midrange Systems Technical Discussion
Betreff: Re: Date failing issue in SQL

in the place of varchar, ( date(varchar(integer(T01.IATRND/10000)) || '-'
||) use DIGITS. varchar will drop leading zeroes, a common cause of this
issue.

On Thu, Oct 13, 2016 at 9:20 AM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> 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'
I checked against this date IATRND, via a query/400 and see nothing funky.

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

problem?

SELECT
ALL
T01.IAPRT#, T01.IA101, T01.IATRND,T01.IAUSRN,T01.IAPRLC, T01.IARCC1,
T01.IATRT, T01.IARCC2, T01.IARCC3, T01.IARCC4, T01.IARCC5,
T01.IAACTF, T01.IATRNC,
T02.IABMU, T01.IARCC6, T01.IARCC7, T01.IARCC8, T01.IARCC9,
T01.IARC10, T02.IAHCD1, T02.IAHCD2, T02.IAHCD3, T01.IAPTWT,
T01.IARC11,T01.IARC12, T01.IARC13, T01.IARC14, T01.IARC15
FROM ASTDTA.AUDIA T01,
ASTDTA.ICPRTMIA T02
WHERE T01.IAPRT# = T02.IAPRT#
and date(varchar(integer(T01.IATRND/10000)) || '-' ||
varchar(integer(T01.IATRND/100) - integer(T01.IATRND/10000) *
100) || '-' ||
varchar(mod(T01.IATRND, 100))) = current date
--
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.