RE: SQL Default Value for Date Fields

Implicit in Charles' response is that if you want to make SQL behave the
same as RPG, you can change the default value to whatever you want. Since
table already exists you can ALTER it with something like:

ALTER TABLE QTEMP/T1
ALTER COLUMN F1
SET DATA TYPE DATE
NOT NULL WITH DEFAULT '0001-01-01'


I also recommend changing your creation script to set the default to what
you want in case you regenerate the database in the future.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL Default Value for Date Fields

The default definition of fields is "NOT NULL WITH DEFAULT"

Char fields default to blanks
Numeric fields default to 0
Date fields default to current date.

So when you issue an SQL insert, any date field you don't give a value to
would get today's date.

The RPG program on the other hand, passes every field and is responsible for
setting its own date
default, which happens to be 0001-01-01.

HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Mark Allen
Sent: Thursday, August 07, 2008 10:46 AM
To: Midrange Systems Technical Discussion
Subject: SQL Default Value for Date Fields

We just ran across this and I am "kornfused".

When I do an insert thru a program (RPG) or DFU date fields not explicitly
entered have a value of 01-01-0001 BUT

When an SQL INSERT is done (from both the 400 and our .NET app) those same
fields default to the "Current Date" (those done today get 08-07-2008)??

Is there something I am missing in the file definitions or SQL to make SQL
put the '01-01-0001' in fields not specifically gvien a value?