× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Db theory suggests using NULL for the TermDate, as null indicates 'void of
value' which fits in this scenario.

That said, based on my experience in working with System i customers, many
RLA programmers have a hard time working with NULLs and avoid them like a
plague. So, for practical reasons, you're better off using some default
value instead of NULL. Default value that I like the best is 0001-01-01 as
it gives you a very large range of valid dates. You HAVE TO specify it in
your DDL (can't omit it), as DB2's default for a date column is CURRENT_DATE
(obviously, current_date is functionally wrong in this case and you can't
live with DB2's default).

Using 0001-01-01 implies that you should be using *ISO for your Date Format,
but obviously you're free to use whatever Date Format you want. I like *ISO
for its lack of ambiguity.


Try this in interactive SQL as I think it'll help you decide very easily
what you'd like:

1)
CREATE TABLE QTEMP/testDates (
F1 DATE ,
F2 DATE NOT NULL WITH DEFAULT '0001-01-01',
F3 DATE NOT NULL WITH DEFAULT)
2)
insert into qtemp/testDate values(default,default,default)

Now, test the output using two different formats. First hit F13 and test it
using *MDY format (default I think):

1) SELECT * FROM QTEMP/TESTDATE

And now test it using *ISO Date Format (hit F13 again to change it to *ISO):

2) SELECT * FROM QTEMP/TESTDATE

See the difference Date Format makes when displaying the (same) date value?

Hth, Elvis

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


-----Original Message-----
Subject: Interesting question and debate on ddl tables with date fields that
will not always have a value

I hope that subject description makes it clear. We are having an
internal
debate and discussion as we make the move towards sql tables and ddl
files; specifically using date data types.

An example, an employee file has two dates, HireDate and TermDate, self
explanatory.....one is when the person is hired and the other is when
they
leave.

Obviously while they are an employee, they do not have a term date, so
how
do we handle that?

suggestions? comments? thoughts?



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.