Hi,
WHERE ORDDAT BETWEEN 01/01/1960 AND 31/12/2000
What data typ is ORDDAT? Is it a (real) date or a numeric date?
If it is a real date, you have to embed your Dates into single quotes
(otherwise it calculates 1/1/1960 !:
WHERE ORDDAT BETWEEN '01/01/1960' AND '31/12/2000'
But you need to use a valid date format for the character representation,
i.e.
ISO: YYYY-MM-DD
USA: MM/DD/YYYY
EUR: DD.MM.YYYY
There is no need to convert the character representation of the dates into
the *ISO format (YYYY-MM-DD).
SQL is smart enough to interpret the date representation, i.e. it looks for
the position of the 4 digit year and the separator (/= USA and . = EUR).
Under the cover a date is a numeric value (Scaliger number) and SQL does not
care about any date formats specified in DDS described files.
It just takes the Scaliger number and uses the date format of the current
job/connection for displaying it.
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)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of techie21
IT
Sent: Freitag, 17. Juli 2020 23:07
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: filtering records between 2 dates
my sql statement is like below:-
SELECT * FROM p2
WHERE ORDDAT BETWEEN 01/01/1960 AND 31/12/2000
Comparison operator BETWEEN operands not compatible.
File P2 is having ORDDAT field as a date field and is defined using 'L'
data type.
So I want to filter records between some specific date range but unable to
do so because of the mentioned error.
So need guidance how could i remove this error to get desired results?
Thanks much..
On Sat, Jul 18, 2020 at 1:52 AM techie21 IT <techiei876@xxxxxxxxx> wrote:
experts any updates please?
On Sat, Jul 18, 2020 at 12:53 AM techie21 IT <techiei876@xxxxxxxxx> wrote:
hi,
when trying to filter records between 2 dates getting this error:-
Message ID . . . . . . : SQL0401 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Message . . . . : Comparison operator BETWEEN operands not compatible.
Cause . . . . . : The operands of comparison operator BETWEEN are not
compatible.
-- Numeric operands are compatible with any other numeric
operands and
with character and graphic operands.
-- Character operands are compatible with operands that are
character,
graphic, date, time, timestamp, or numeric.
-- Date, time, and timestamp operands are compatible with
character and
graphic operands or with another operand of the same type. Date and
timestamp operands are also compatible.
-- Graphic operands are compatible with graphic, character, date,
time,
timestamp, or numeric operands.
-- Binary operands are compatible only with binary operands.
More...
Press Enter to continue.
F3=Exit F6=Print F9=Display message details
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
