Re: SQL cursor inputting default dates from a DDS file

To work with literal dates in the RDi (ACS) "Run SQL Scripts" window, you
may need to cast the date like this:
yada yada = DATE('0001-01-01').

Here's a piece of code (SPS030 accepts three date data-types) to determine
if a date (2020-12-31) is between the effective and cancel dates including
when the cancel date is '0001-01-01':
AND sps030(date('2020-12-31'), gpbeff, gpbcan) = '1'

Also, remember to do this as the first executable statement in your
SQLRPGLE program:

EXEC SQL
SET OPTION CLOSQLCSR = *ENDACTGRP,
commit = *CHG,
DATFMT = *ISO,
TIMFMT = *ISO;

This ensures dates not specifically cast as ISO default to ISO; it's bitten
me twice.

On Tue, Jan 26, 2021 at 1:13 PM Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

Good afternoon Ladies and Gentlemen,

I'm trying to do a quick RPG query with an SQL cursor, but I need to be
able to input three dates, any one of which has '0001-01-01' in the
filtered subset I will try to fetch. Ran my statement in the separate
SQL scripting window (from RDi) but it threw the error that there is a
conversion error. I"m sure the above zero-one date is the culprit.

I'm sure I solved this once, years back, but if anybody has the quick
answer to as to how I can cast those dates, I'd sure appreciate it and
would send good vibes your way!

(I wouldn't even mind if our global SQL guru piped in her thoughts! You
know who you are!)

--aec

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