AW: SQL Date Problem

Hi,

1. For all date formats with a for digit year (*ISO, *USA, *EUR, *JIS) ,
   the date separator is fixed and cannot be specified.
   DAPIISNSTIND must be defined with date format *ISO.
   '-' is the date separator that is mandatory for the date format *ISO

2. To convert a date into a character representation, you do not have to
convert a date into a date:
   Inv_Date = %Char(DAPIINSTIN: *USA);
   should work.
   The internal representation of a date is a 4digit binary value.
   The second parameter in %CHAR() defines the output parameter.

3. The SQL set option parameter has nothing to do with the RPG date
calculation,
   but with the date format that is used in SQL statements.
   The precomiler converts the SQL statements into API calls.
   For each specified field in the SQL statements additional workfields are
created.
   The date format specified in the set option statement, is the date format
that
   is used for those workfields.

For more information about Date and Time calculation in RPG and SQL see
Chapter 9
in the following redbook:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Mike Wills
Gesendet: Mittwoch, 16. März 2005 23:34
An: Midrange_RPG400_L
Betreff: SQL Date Problem


Hey guys,

I am getting some compiler errors and need help getting them fixed. My
database has been created in SQL (not DDS) and are now using actual
date data types. I am now trying to process these fields in an RPG
program.

In my compiler listing, my field I want is defined as:
     D DAPIINSTIN                    10D   DATFMT (*ISO-)
which is brought in via:
     D gResultDs     E DS                  EXTNAME(DBAPAPI)

So now I want this to print on a report. Not knowing exactly how to do
this, I decided to define the field as text like this:
     A            INV_DATE      10   O    +1

Then I used this code to get it into this field.
inv_date = %char(%date(dapiinstin:*USA));
I want to get the date to display as 04/02/2005. This gave me a
compiler error saying "The first parameter of %date is not the correct
format" or something close to that. So after talking to another person
I know, we talked about trying this:
inv_date = %char(%date(%char(dapiinstin):*USA));
This gives me this error: "The types of the right and left hand side
do not match in the EVAL operation."

So my question is, how do I get this to work?

I did see in the archives something about this:
You need this at least to prevent your error...
     C/EXEC SQL
     C+ SET OPTION DATFMT = *USA
     C/END-EXEC
But I don't know if this would help me in this case.

Thanks for any help!

--
Mike Wills
Midrange Programmer/Lawson Administrator
koldark@xxxxxxxxx
http://www.mikewills.name
Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.