A little off the SQL multiple date formats in same table topic, but this
post made me curious. So I did some tests...it does affect some things if
you use an "incompatible" date format.
I created a test file using ISO, USA, and MDY as the defined format (DDS).
Then I inserted 1900-01-01 into each column using SQL. Worked just dandy.
SQL Select showed the dates in *ISO (my session format). Changing the date
format to *MDY, they all show as ++++++++.
DBU blows up with divide by zero error.
UPDDTA won't show the record (a data or key conversion error occurred.)
WRKQRY shows the ISO and USA in the correct format but shows ++++++++ for
the MDY date (which makes sense!). I could change the "format" by using
char(datemdy,ISO). But, if I tried to change it back to a date
(date(char(datemdy,iso))), it just shows ++++++++ (using either one or two
result field steps). This appears to be because of the job format only
allowing 2 digit years (I've never noticed that you can't set your job date
format to ISO or USA-guess I never needed to do that).
An RPGLE program...
ftestfile if a e disk
fqsysprt o f 132 printer
/free
dateiso = d'1900-01-02';
dateusa = d'1900-01-02';
datemdy = d'1900-01-02';
write testrec;
setll 1 testrec;
read testrec;
except line1;
read testrec;
except line1;
*inlr = *on;
/end-free
oqsysprt e line1
o dateiso +1
o dateusa +1
o datemdy +1
Compiles fine, but blows when trying to write testrec (yep, on the write-it
must do some edit checking before chucking it in the file, but doesn't
really care about the field itself).
If I change it to print line1 after setting the dates, they all print as ISO
dates.
If I take out the write (just try to read the existing record), I get a
CPF5029 when reading the file.
Creating a display file using them as reference fields causes similar errors
with the mdy results, but the other fields will display with proper editing
(and the fields are edit checked correctly on the screen).
How come RPGLE output doesn't use the "correct formatting"? He asks
rhetorically. Answer: I believe it is about "older" options for datfmt that
cause all the output to default to the datfmt. Change the program above to
include H datfmt(*mdy) and the output appears in MDY format. If you do that,
you will also have to change the d'1900-01-02' to valid MDY formats to pass
the compile. If you don't include the datfmt option, you will get ISO dates
by default.
Bottom line, SQL doesn't care (assuming you have ISO for displaying dates)
but many other products care at least a little depending on what you are
trying to do.
Mike Krebs
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, August 28, 2007 10:37 AM
To: 'Midrange Systems Technical Discussion'
Subject: SQL multiple date formats in same table
I think I learned something.
I wanted to define a file via DDL with one date field *ISO and 1 date field
*USA. I couldn't figure out how to do it, so I created a test file with DDS
defining the date fields like this:
A R TESTREC
A DATEISO L TEXT('ISO date')
A DATFMT(*ISO)
A COLHDG('ISO' 'Date')
A DATEUSA L TEXT('USA date')
A DATFMT(*USA)
A COLHDG('USA' 'Date')
then I retrieved the SQL source for this DDS defined file. Both fields came
out exactly the same way:
-- Generate SQL
-- Version: V5R4M0 060210
-- Generated on: 08/28/07 11:22:50
-- Relational Database:
-- Standards Option: DB2 UDB iSeries
CREATE TABLE DBMSTF/TESTFILE (
DATEISO DATE NOT NULL DEFAULT CURRENT_DATE ,
DATEUSA DATE NOT NULL DEFAULT CURRENT_DATE )
RCDFMT TESTREC ;
LABEL ON COLUMN DBMSTF/TESTFILE
( DATEISO IS 'ISO Date' ,
DATEUSA IS 'USA Date' ) ;
LABEL ON COLUMN DBMSTF/TESTFILE
( DATEISO TEXT IS 'ISO date' ,
DATEUSA TEXT IS 'USA date' ) ;
Since the date format is for presentation purposes only, does this mean that
I cannot use multiple date formats in an SQL table? That the date format is
set by the Set Option DatFmt = ??? Statement alone?
If so, I'll work around it, but it just surprised me. I guess it makes
sense, though, since the date is 'stored on disk' the same irrespective of
the date format.
--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
