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.