RE: SQL date problem

Larry,

good idea

Better idea:  Use something like the following.  That way you don't have 
to remember to change your compile defaults.
     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     Datfmt    = *iso,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Jansen, Larry" <ljansen@xxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/12/2004 11:34 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: SQL date problem






When you compile the program, specify values for the parameters DATFMT and
TIMFMT, probably *ISO for both, if that's what your defaults are for your
data.

Larry Jansen

-----Original Message-----
From: Carmen Nuland [mailto:cnuland@xxxxxxxxxxxxxx]
Sent: Thursday, August 12, 2004 12:23 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL date problem


I am relatively new to the SQLRPGLE arena, and am having a problem with a
date field.  In this SQL statement:

C/EXEC SQL 
C+ Declare C1 cursor for 
C+ select Line#, Control, Text from DETAIL
C+ where Custno   = :Scn_Custno 
C+   and SentDate = :Scn_Date
C/END-EXEC 

My problem is that in a particular example, it isn't going to find any
records in DETAIL.  When I run this SQL statement interactively, it just
returns a blank screen.  When I run it in RPG, I get the following message
(RNX0114):

Message . . . . :   The year portion of a Date or Timestamp value is not 
in

  the correct range.

Cause . . . . . :   A conversion was requested to a Date value, from a

  Timestamp or a Date value, and the year of the source Timestamp or Date 
is

  not in the allowable range of the target.

  -- The range for a 2-digit year is 1940-2039.

  -- The range for a 3-digit year is 1900-2899.

  -- The range for a 4-digit year is 0001-9999.


I'm assuming this has something to do with returning null values, but I'm
not sure how to fix it.
Carmen


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