RE: SQL Default Value for Date Fields

And on the other hand, if you wanted RPG to work like SQL, I think you could use write the record
using a data structure defined with EXTNAME and INZ(*EXTDFT).

Just to cover all the bases :)

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 Elvis Budimlic
Sent: Thursday, August 07, 2008 11:29 AM
To: 'Midrange Systems Technical Discussion'
Subject: 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?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.