Re: filtering records between 2 dates

This is how I do it in my database:

select * from p2 where DOB BETWEEN '1970-01-01' and '1990-12-31';


Paul

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of techie21
IT
Sent: Friday, July 17, 2020 5:54 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: filtering records between 2 dates

select * from p2 where DOB BETWEEN '01/01/1970' AND '31/12/1990'
Value in date, time, or timestamp string not valid.
Session was saved and started again.
Current connection is to relational database PUB400.
select * from p2 where DOB BETWEEN '01/01/1970' AND '31/12/1990'
Value in date, time, or timestamp string not valid.
select * from p2 where DOB BETWEEN '1970/01/01' and '1990/12/31'
Syntax of date, time, or timestamp value not valid.
Session was saved and started again.
Current connection is to relational database PUB400.

tried all the ways still this error is not getting removed and as already
mentioned DOB or ORDDAT field is date field and mentioned in DDS as
DATFMT(*ISO) clearly.

On Sat, Jul 18, 2020 at 3:20 AM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Interesting, Peter - the date() function is very forgiving in this case.

Now I have to ask, should the OP wrap the 2 date values with a DATE
function, or should the OP use the syntax as Joe presented it? I
prefer the latter, esp. because function calls in WHERE clauses CAN
result in a full table scan - not likely here, where the function is
run against a literal, though.

Regards
Vern

On 7/17/2020 4:44 PM, Peter Dow wrote:

Yes, ORDDAT is a date. Format does not matter except for display
purposes.

01/01/1960 is not a date. Try this:

select 01/01/1960 from sysibm/sysdummy1

Now try this:

select '01/01/1960' from sysibm/sysdummy1

And finally, try this:

select DATE('01/01/1960') from sysibm/sysdummy1

Look at the headings in each case.

The 1st one thinks 01/01/1960 is some sort of number.

The 2nd one thinks '01/01/1960' is just a literal constant.

The 3rd one recognizes that it's a date.




On 7/17/2020 2:19 PM, techie21 IT wrote:

even though i have specified it as data type 'L' and with DATFMT
keyword
(*ISO) still i am getting this error.

On Sat, Jul 18, 2020 at 2:39 AM Joe Pluta
<joepluta@xxxxxxxxxxxxxxxxx>
wrote:

I always try to use ISO dates:

where ORDDAT between '1960-01-01' and '2000-12-31'


On 7/17/2020 4:06 PM, techie21 IT wrote:

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

--
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

--
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