RE: SQL multiple date formats in same table

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Hi Jeff

This won't make any difference to what you do - 
but this is kind of interesting--
As Birgitta said, all dates are stored as a 
4-byte integer - the date format you specify is 
the way the date is displayed - yes, even in 
DSPPFM, which does convert to that specified 
format - *ISO is default, by the way. Internally, 
different formats for the same date will all look 
the same - the difference comes when those dates 
are filtered through the format.

Now an analogous situation comes to mind - packed 
or zoned fields - a 7,2 packed field is stored in 
4 bytes, and so is a 6, 0 - in both of these, the 
storage bytes of 235.00 for the first, and 235 
for the second, will look exactly the same - the 
format is the filter that determines how things are processed.

One weird things is, DSPPFM converts dates to the 
format used, whereas number are not converted - packed or binary.

SQL knows naught about record formats, and it 
displays dates in the default job date format 
when you are in STRSQL.Take your file with 
different formats for the date and use a SELECT * 
on it in STRSQL - you'll not see those date 
formats, rather, the job date format.

Fun, huh?
Vern

At 02:50 PM 8/28/2007, you wrote:

> To answer the "why" it's because that's the way it was in DDS.  You could
> define different date fields within the same file with different date
> formats, and that determined how the date was displayed.  I _always_ store
> dates in *ISO format unless there's a compelling reason not to.  In this
> case, this new file is ultimately going to be FTP'ed weekly to another
> company.  They want the tran date as mm/dd/yyyy.  For my own purposes I put
> a RPTDATE (report date, *ISO) field into the file so later I would know what
> transactions were sent on what date.  I'll just handle the date conversion
> when I transmit.
>
> --
> 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.
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of BirgittaHauser
> > Sent: Tuesday, August 28, 2007 2:52 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: AW: SQL multiple date formats in same table
> >
> > Hi,
> >
> > First question why would you do so?
> >
> > A date is always stored as 4Byte binary value, representing
> > the number of calculated days since 01/01/0001.
> > Date Formats are only used to make this 4Byte binary value readeable.
> > In your application you may specifiy the date format to use.
> > For example in interactive SQL over F13=Service and Option 1
> > --> DatFmt For embedded SQL you can specify the date format
> > at compile time in the compile option DATFMT or within an SET
> > OPTION-Statement.
> >
> > If you want to print or display a date in a specific date
> > format, you have to convert the date into a character string
> > by using the scalar function
> > CHAR:
> > Select Char(MyDate, USA) as DateUSA, Char(MyDate, ISO) as DateISO
> > >From ....
> >
> > In either way it is not possible to specify a date format in
> > the SQL CREATE TABLE Statement.
> >
> > 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 Jeff Crosby
> > Gesendet: Tuesday, August 28, 2007 17:37
> > An: 'Midrange Systems Technical Discussion'
> > Betreff: 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.