RE: SQL multiple date formats in same table

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.