AW: SQL multiple date formats in same table

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

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.