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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2021 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.