Hi,
A date is always stored in a 4Byte integer value, representing the
calculated number of days since 01-01-0001.
Date formats are only used to make this numeric value readeable.
If a date format with a 2 digit year, such as MDY is used, the valid range
that can be shown is between 01-01-1940 and 12-31-2039. Dates outside this
valid range are displayed as invalid date (+++++++++).
Contrary to RPG which blows up when using a date outside the valid range,
SQL let you insert dates outside the valid range, but will display it as
invalid date.
Just try to use a date between 1940 and 2039 in your example and a valid
date will be displayed.
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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mike Krebs
Gesendet: Thursday, September 06, 2007 00:17
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL multiple date formats in same table
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
