Re: To Date or Not to Date... that is the question

Dave,

The internal format, as stored below MI, is a 4-byte integer representing a Scaliger value for the date. When using above MI interfaces, such as the i5/OS commands DSPFFD and DSPPFM or above MI database operations such as reading a record, you generally see a formatted 10-character date value representing the "physical" buffer layout. The "physical" record you are seeing is an above MI view of the world. SQL can bypass this mapping of internal to "physical" using functions such as HEX. But actually stored on disk, in what you think of as a file, is a 4-byte integer value.

Bruce Vining

Dave Kahn <dkahn400@xxxxxxxxxxxxxx> wrote:
On 18/12/2007, CRPence wrote:

DSPFFD does indeed effectively lie for the length of data types Date,
Time, and TimeStamp. The /buffer length/ is accurate for the specific
[human-readable] formatting for presentation, not the length for the
internal storage for the data type; as seen for Packed and Integer for
example.

But what about the storage in the physical record? I create a database
file with a date field DSPFFD tells me the field occupies 10 bytes. If
I populate the database file and display it with DSPPFM I see a 10
character ISO representation of the date. If I then create a new flat
physical file and copy the database file to it with FMTOPT(*NOCHK) I
see that 10 character format dates are copied to the flat file. There
should have been no under-the-covers conversion in this case.

If I create an RPG program defining my database file as a program
described flat file and use it to read the file I can see that it is
indeed getting 10 character representations of the date in the buffer
position indicated by DSPFFD.

If I use SQL to display my database file the date field is not
displayable unless I use a function such as HEX, CHAR or DAYS to
resolve it.

All the above suggests to me that the physical representation in the
database is not the internal date format but the character
representation and DSPFFD is telling the truth about the buffer. It
appears to me to be the human readable form that gets stored in the
database.