dates in rpg

20 pts.
Hi! I'm starting to use RPG AS/400, and I'd like to know how to insert date fields in a table using strsql. Can anyone help me, please? Thanks in advance, MelissaG

Answer Wiki

Thanks. We'll let you know when a new response is added.

The file I’m using has only two fields, DATE1 and DATE2, both of which use the default *ISO format. From a STRSQL session:

insert into dates00
values (current date, ‘2005-01-01’)

“current date” will supply just what it implies, in the proper format. The hardcoded date must be valid and match the format of the date field.

update dates00 set date1 = ‘2004-01-31’

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Cmasse
    HI, You must use the format,as define via F13, with STRSQL. In France, the default format is *DMY, so i use 'DD/MM/YY', but if you use the internationnal *ISO format, use 'YYYY-MM-DD' (wich is the default format with JDBC/Operation Navigator). So , look your session format with F13 in STRSQL, first. Christian.
    0 pointsBadges:
  • TimHawkins
    CURRENT_DATE results in a date data type, you must first convert it to a numeric (*ISO) value. This function will do that. Create the function and then use it like: insert into filename (cvtdtetoiso(CURRENT_DATE), 20050622) -- the lines got screwed up a little, but I'm sure you can figure out what would look nice. "yoursystemlibraryname" is not QSYS, its a system library above all other libraries, we store SQL functions and system wide overrides there. No source. -- Convert from date to an ISO date CREATE FUNCTION yoursystemlibraryname/CvtDTEtoISO (dte date) RETURNS decimal(8,0) LANGUAGE SQL SET OPTION DATFMT=*ISO, TGTRLS=V5R3M0 BEGIN DECLARE F_OUTPUT decimal(8,0); DECLARE F_TEST INTEGER ; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ; SET F_TEST = 0; SET F_OUTPUT = DECIMAL((YEAR(dte)*10000 + MONTH(dte)*100 + DAY(dte)),8); IF F_TEST = 0 THEN RETURN F_OUTPUT ; ELSE RETURN NULL; END IF; END
    0 pointsBadges:
  • TomLiotta
    I'm starting to use RPG.. and I'd like to know how to insert... using strsql. RPG and STRSQL have almost no relationship. RPG is a programming language and STRSQL is a SQL command-line utility. You can INSERT into DATE columns by using a value that is already formatted correctly, by using a value that comes from another DATE variable or by CASTing a value into a DATE value, and possibly other ways. If some examples of what was tried were shown, we could offer corrections or alternatives. Be aware that SQL statements from STRSQL often won't work the same when embedded in a program. Programs commonly use host variables which can introduce some subtle differences. Tom
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: