Re: Date/Time fields in DB file

Greg


What are the default values for those fields? I know one can set defaults this way -


ALTER TABLE DP2TEST/LONGNAMES ADD COLUMN NEWDATE DATE NOT NULL WITH DEFAULT current date
ALTER TABLE DP2TEST/LONGNAMES ADD COLUMN NEWtime time NOT NULL WITH DEFAULT current time


At least with DDL-based tables. Then an INSERT (maybe a WRITE - it's been too long, I can't remember) like this will use date and time of when the action took place.


INSERT INTO DP2TEST/LONGNAMES (LNAMENOBRK, LNAMEBRK, LNAMEMXD) VALUES('x', 'y', 'z')

If you can't do this for DDS files, here is something interesting from the DDS manual -



* If you are defining a date field, specify a valid date in the same format specified on the DATFMT keyword and use the same separator as specified on the DATSEP keyword. For example, DFT('12/15/05') is the default value if *MDY is specified for DATFMT and '/' is specified for DATSEP.
If the DFT keyword is not specified, the default value is the current date.
* If you are defining a time field, specify a valid time in the same format specified on the TIMFMT keyword and use the same separator as specified on the TIMSEP keyword. For example, DFT('11.00.00') is the default value if *ISO is specified for TIMFMT. The default separator for *ISO is a period (.).
If the DFT keyword is not specified, the default value is the current time.
* If you are defining a timestamp field, you must specify the default value in the following format:DFT('YYYY-MM-DD-HH.MM.SS.UUUUUU')
If the DFT keyword is not specified, the default value is the current time.

This looks like you should leave the DFT keyword off, then do not include the date and time in the field list that you use when writing the record.


Maybe?


Cheers
Vern

On Thu, 10 Aug, 2023 at 1:10 PM, Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx> wrote:


To: midrange systems technical discussion

Greg,

A "Before Insert" Trigger should do this easily and cleanly.

HTH,
Mark

On Thursday, August 10, 2023 at 01:57:16 PM EDT, Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>> wrote:

I recently added an ISO date and ISO time field to an existing database file (DDS). This file is populated by our TrustedLink EDI software when we receive an inbound PO. I guess I had hoped it would capture the date/time the record was created. Tli support says the EDI translator won't update those "automatically".

Anyone know of a method of getting these fields to update with current info upon creation? Otherwise, I'll have to update them in my RPG processing program.

TIA,
Greg