On 18-Jul-2016 15:04 -0500, Stone, Joel wrote:
Is writing a date value of 01/01/0001 a good idea?
Or is setting field to null work better?
When using alpha fields in the past, many folks would code to put
'00000000' in any alpha or numeric date field that was undefined.
What is a good approach for iSeries date fields on add?
Seems an issue more generally, about the database, than specific to
any language. Admittedly, implementation\support for NULL values is
going to be specific to the HLL being used; even somewhat when using SQL
vs RLA.
The decision typically is made by a[n effective] DB Architect in
support of the application(s); with consideration of course, for any
legacy applications\programs that might be impacted by a change being
made to the file(s) for which the question\issue arose. What is /best/
is surely a decision that can only be made by those owning the database,
the data, and the application. Regardless, I offer:
From those three questions [quoted above], perhaps the "Subject:"
question meant to ask more specifically, what should be the DATE value
written when a value for that DATE data-type field is undefined [i.e. a
specific value can not be assigned] within the application [program of
source-type COBOL]? In response to that, I would suggest either the
Default value [assigned for the column] or the database NULL value would
be the most likely choice to have written.
The DATE value 0001-01-01 is likely only _good_, when that value is
defined as the preferred default value. If existing and new programs
are not [going to be] written to handle NULL values, but to handle all
valid non-NULL date values, then that 01/01/0001 value is probably an
acceptable date value used to represent a default\unassigned value.
However if instead, the programs are [going to be] written to handle
only the date values within the 100-year window, then 01/01/1940 might
be considered a more appropriate choice; i.e. a likely requirement will
be to avoid an effective date-underflow error that would result when
using a two-digit year presentation format. The values 1940-01-01 and
0001-01-01 might serve as an effective base-date, as a date which
represents the past or a distant past that is out of range for the
domain of dates being represented by the column; nonetheless, they are
problematic, because they are valid dates. They are not [or are less]
problematic when they are knowingly outside the domain of dates that
must be capable of being represented; the latter date value clearly is
outside the domain of dates satisfying the BirthDate of all living
persons, and the former is outside the domain of dates satisfying all
Employee401kPlanStartDate -- applications [and queries] are written to
know these otherwise _illogical dates_ represent something distinct from
the logically acceptable dates.
Because the database NULL value means, effectively, that there is no
value assigned, then that is likely to be the /best/ choice, *if* the
programs are [going to be] written to handle the processing of the NULL
value. Yet, numerous debates about whether or not the NULL value even
should be used, are probably easily found one the web.
The DATE data type only allows for either the NULL value or any valid
non-NULL date value to be written; i.e. character strings that can not
be cast to a valid date, can not be written like could have been done
with data types of character or numeric used to represent DATE data. If
an unassigned date value has more than one meaning, then the database
likely will have to be designed specifically to resolve that; by
prevention. For example, the NULL value for a date value not yet
assigned, would not easily represent simultaneously, both the condition
of NotYetHired and condition of HiredButNotStarted for an
EmployeeStartDate column; not without additional reference to the value
for an EmployeeHireDate, for example. However using a character data
typed column, the value '00/00/0000' might represent NotYetHired and the
'99/99/9999' might represent HiredButNotStarted, without any requirement
to know anything about any EmployeeHire… details.
midrange.com
