Re: Question on buffer lengths vs SQL reported lengths

Sorry for jumping in so late, but I was out most of October and am just now
trying to get "up to date".

--The physical storage method for a date is to store it as a 4 byte count
of the number of days since ... (Can't recall).
--
--BUT you can really not easily ever see it like that. The operating system
always surfaces it in its edited 8 or 10 character form.

The number of days is based on the Scaliger Julian period starting January
1, 4713 BC (using the Julian calendar). This would then result in an *ISO
date of 0001-01-01 being represented by the binary value 1721426 or
x'001A4452'. This can be verified by setting a date column (date1) of
table dates to 0001-01-01 and then using the SQL statement 'Select date1,
hex(date1) from dates'. The result (assuming your SQL session date format
is *ISO) is:

....+....1....+....2....+
DATE1 HEX ( DATE1 )
0001-01-01 001A4452

This is also confirmed in the Information Center at
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzatk/MINDTCON.htm
(though you'll have lots to read before you get to):

"The SAA calendar table has 2 entries. The first entry has a calendar type
of Gregorian. The effective date is January 1, 0001, Gregorian for the
start of the time line. The internal format would be 1721426. The second
entry has a calendar type of null. The effective date is January 1, 10000,
Gregorian for the end of the time line. The internal format would be
5373485."

On Thu, Oct 10, 2019 at 2:05 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

The physical storage method for a date is to store it as a 4 byte count of
the number of days since ... (Can't recall).

BUT you can really not easily ever see it like that. The operating system
always surfaces it in its edited 8 or 10 character form.

Even the compilers can't see the "raw" 4 bytes - but nevertheless that is
how it is physically stored/

On Oct 10, 2019, at 10:18 AM, Matt Olson via MIDRANGE-L <

midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Anyone have any ideas why if I do a "select * from qsys2.syscolumns

where table_schema='yourschema' and table_name='tablename'" and you look at
the STORAGE or LENGTH columns for a DATE field it says "4"

But when you do a DSPFFD against the same file you will see the field

length is 10.

I've noticed a discprency on TIME fields as well. Where the

qsys2.syscolumns STORAGE and LENGTH columns say "3", but DSPFFD shows 8

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com