A date (data type L) in a DDS described table or an SQL Table (data type
DATE) is internally stored as integer value (scaliger number) representing
the number of days since x.
Date formats are only used to make this integer value readable. (You can
check the hex value of a date by executing an SQL Select Statement SELECT
HEX(MyDate) ...).
In DDS described files you can specify a date format, while SQL tables do
not support any date format.
The date format in DDS described tables was needed to make the date readable
with WRKF.
SQL always uses the current date format to make the integer value readable.
Just execute an SQL SELECT statement on your DDS described physical file.
The change the data format in your SQL environment (for example STRSQL, F13,
Option 1) and reexecute your SELECT statement again.
In RPG the date is always converted into a character representation (that's
why you won't see the integer value when debugging your program).
But the character representation is converted into the scaliger number as
soon as the record is written.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Thomas Garvey
Gesendet: Wednesday, 27.6 2012 18:14
An: rpg400-l@xxxxxxxxxxxx
Betreff: I guess I don't know dates
I'm perplexed by something that must be very simple, yet I must be ignorant.
I can't get date data type fields populated in a PF correctly.
I have a DDS physical file with date data type fields defined with various
date formats (*USA, *ISO, *MDY, *JUL, etc.). The fields that permit use of
the DATSEP parameter do have it specified.
This file is populated (by RPGLE program X) from another file with numeric
values in the form of YYYYMMDD. Program X has stand alone date data type
fields defined with each of the various formats and separators (example
below).
d DateMDY s d datfmt(*MDY )
mm/dd/yy
d DateMDYSlsh s d datfmt(*MDY/)
mm/dd/yy
d DateMDYDash s d datfmt(*MDY-)
mm-dd-yy
d DateMDYPerd s d datfmt(*MDY.)
mm.dd.yy
d DateMDYComm s d datfmt(*MDY,)
mm,dd,yy
d DateMDYAmph s d datfmt(*MDY&)
mm dd yy
d DateDMY s d datfmt(*DMY)
dd/mm/yy
d DateDMYSlsh s d datfmt(*DMY/)
dd/mm/yy
d DateDMYDash s d datfmt(*DMY-)
dd-mm-yy
d DateDMYPerd s d datfmt(*DMY.)
dd.mm.yy
d DateDMYComm s d datfmt(*DMY,)
dd,mm,yy
d DateDMYAmph s d datfmt(*DMY&)
dd mm yy
d DateYMD s d datfmt(*YMD)
yy/mm/dd
d DateYMDSlsh s d datfmt(*YMD/)
yy/mm/dd
d DateYMDDash s d datfmt(*YMD-)
yy-mm-dd
d DateYMDPerd s d datfmt(*YMD.)
yy.mm.dd
d DateYMDComm s d datfmt(*YMD,)
yy,mm,dd
d DateYMDAmph s d datfmt(*YMD&)
yy mm dd
d DateJUL s d datfmt(*JUL)
yy/ddd
d DateJULSlsh s d datfmt(*JUL/)
yy/ddd
d DateJULDash s d datfmt(*JUL-)
yy-ddd
d DateJULPerd s d datfmt(*JUL.)
yy.ddd
d DateJULComm s d datfmt(*JUL,)
yy,ddd
d DateJULAmph s d datfmt(*JUL&)
yy ddd
d DateISO s d datfmt(*ISO)
yyyy-mm-dd
d DateISODash s d datfmt(*ISO-)
yyyy-mm-dd
d DateUSA s d datfmt(*USA)
mm/dd/yyyy
d DateUSASlsh s d datfmt(*USA/)
mm/dd/yyyy
d DateEUR s d datfmt(*EUR)
dd.mm.yyyy
d DateEURPerd s d datfmt(*EUR.)
dd.mm.yyyy
d DateJIS s d datfmt(*JIS)
yyyy-mm-dd
d DateJISDash s d datfmt(*JIS-)
yyyy-mm-dd
So, my program X logic moves the numeric value (in YYYYMMDD format) into the
DateISO field (defined as *ISO) and then moves the DateISO into each of the
stand alone fields shown above. The resulting values are all correct as
defined. That is, they contain exactly what they should contain (the same
date value, but in the field's format and separator). This is verified
using debugger.
However, when these values are then moved into the fields as defined in the
DDS PF (definition example shown below), they all get *MDY. Doesn't matter
how they are defined in the DDS.
A DUSASLSH L DATFMT(*USA)
A COLHDG('Date' 'USA Slash')
A DISODASH L DATFMT(*ISO)
A COLHDG('Date' 'ISO Dash')
A DEURPERD L DATFMT(*EUR)
A COLHDG('Date' 'EUR Perd')
A DMDYSLSH L DATFMT(*MDY)
A DATSEP('/')
A COLHDG('Date' 'MDY Slsh')
A DMDYDASH L DATFMT(*MDY)
A DATSEP('-')
A COLHDG('Date' 'MDY Dash')
A DMDYPERD L DATFMT(*MDY)
A DATSEP('.')
A COLHDG('Date' 'MDY Perd')
A DMDYCOMM L DATFMT(*MDY)
A DATSEP(',')
A COLHDG('Date' 'MDY Comm')
A DMDYNONE L DATFMT(*MDY)
A DATSEP(' ')
What is up with that? What am I ignorant of? What am I missing? There is
no DATFMT specification in any H spec. The system's default format
(QDATFMT) is MDY but I can't believe that is controling here.
I always thought moving a date data type field to another date data type
field simply changes the format of the value.
Straighten me out here, please.
Tom Garvey
--
This is the RPG programming on the IBM i / System i (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.
midrange.com
