Date data types and zero dates
Dave Begley
When converting to dates that will handle the year 2000, I would like to
use the date field instead of expanding the current field to 8 digits.
But many files have dates that are zero (an example is a termination
date field in a file, but the record is not terminated).
I don't think the AS400 will not allow zero dates and this will cause
even more coding changes and force me to go the way of the 8 digit
change. Does anyone have a solution to this?
I know the arguments that zero is not a valid date and that other
databases don't allow zero dates, but I disagree. I know in Access for
instance, I can say 'Allow Zeros = Yes' on a field (and I have done this
with date fields).
Thanks,
!^NavFont02F022A0006IGHHIrB01B
--
- Dave Begley dave_...@sprynet.com
NKP
Dave Begley <dave_...@sprynet.com> wrote in article
<09970503132618.O...@sprynet.com>...
I'm afraid I can't help you, but it seems to me that a date field in any
database should allow zero dates. There's plenty of instances where you
simply don't know a date at the time of writing the record.
--
Nils Kilden~Pedersen
Leonard McCoy
0 is still a value but a null would be "value unknown". This would be a
case where null support would be real nice. I wonder if they will add it to
RPG?
--
Leonard McCoy
Scott Klement
But null values were never available (until very very recently).
What happens too those of us who have over 2000 home-grown programs
that use 6-digit dates that start out as 0's...
I cant convert all these programs to RPG IV, and change the logic to
use nulls instead of zeros in all these programs...
As far as I'm concerned, the lack of support for zeros in the date
data types has made date data types WORTHLESS to me.
Gary Guthrie
Null database field support has already been added to RPG as of V3R7.
Gary Guthrie
CVWD
> I don't think the AS400 will not allow zero dates and this will cause
> even more coding changes and force me to go the way of the 8 digit
> change. Does anyone have a solution to this?
Put *loval into unknown dates and check for *loval as you now check for
*zero.
HTH, Lance
robert davis
Isnt it more like allow null date.
I dont believe I know of any database that allow true 0 year, month,
day.
For a while oracle allowed 0 year and than it changed to year 1. We
found the lowest data that most databases support is 1901/1/1. I think
excel or something forced this constraint.
On the other hand all databases allow null dates. Including as400/db2.
bob
On 3 Jun 1997 17:30:13 GMT, dave_...@sprynet.com (Dave Begley)
wrote:
>When converting to dates that will handle the year 2000, I would like to
>use the date field instead of expanding the current field to 8 digits.
>But many files have dates that are zero (an example is a termination
>date field in a file, but the record is not terminated).
>
>I don't think the AS400 will not allow zero dates and this will cause
>even more coding changes and force me to go the way of the 8 digit
>change. Does anyone have a solution to this?
>
>I know the arguments that zero is not a valid date and that other
>databases don't allow zero dates, but I disagree. I know in Access for
>instance, I can say 'Allow Zeros = Yes' on a field (and I have done this
>with date fields).
>
>Thanks,
>
>!^NavFont02F022A0006IGHHIrB01B
>
>--
> - Dave Begley dave_...@sprynet.com
>
>
Bob Davis
rsd...@nh.ultranet.com
http://www.nh.ultranet.com/~rsdavis/
Jeff Houston
I had this same problem when converting some older apps to use the new
date field. I worked around it by putting 01-01-0001 in the field
instead of zeros. What we really need is good null value support.
Jeff Houston
Dave Begley
> Put *loval into unknown dates and check for *loval as you now check
> for *zero.
I think this probably the best solution, but it leaves room for more
conversion errors if you miss a place where a date is compared to zero.
!^NavFont02F00B40007IGHHGB59EB5
Dave Begley
> Isnt it more like allow null date.
Yes, I think you are correct. I went back and looked at an old database
and all I did was say the field wasn't required. That meant nulls were
allowed, not a zero date.
!^NavFont02F00A40007IGHHGA50443
Jeff Crosby
Scott Klement wrote:
> As far as I'm concerned, the lack of support for zeros in the date
> data types has made date data types WORTHLESS to me.
I wouldn't be that quick to dismiss them. It's obvious you haven't used
them (in conjunction with RPG IV) to any great extent or you wouldn't
make that statement. Date data types have saved me an _incredible_
amount of time handling dates. I spent $250 buying RPG/400 to RPG IV
converter software. Date data types in the database and RPG IV allow
date manipulation that's so easy I can spend my brainpower on what I'm
trying to accomplish instead of dates.
Initializing a date field to "0001-01-01" instead of *Zero takes me 1
extra statement per program. That extra statement is to define a
constant LoDate with the value "0001-01-01". Then instead of:
Eval Date = *Zero
I use:
Eval Date = LoDate
Why would I (or anyone) complain about having to define a single
constant in a program when the date data type saves dozens of statements
and much time in that same program?
We're all entitled to our opinions and mine is that anyone who won't use
date data types simply because you can't put *Zero in there is missing
the whole idea.
--
-Jeff
Scott Klement
Jeff Crosby wrote:
>
> Scott Klement wrote:
>
> > As far as I'm concerned, the lack of support for zeros in the date
> > data types has made date data types WORTHLESS to me.
>
> I wouldn't be that quick to dismiss them. It's obvious you haven't used
> them (in conjunction with RPG IV) to any great extent or you wouldn't
> make that statement. Date data types have saved me an _incredible_
> amount of time handling dates. I spent $250 buying RPG/400 to RPG IV
> converter software. Date data types in the database and RPG IV allow
> date manipulation that's so easy I can spend my brainpower on what I'm
> trying to accomplish instead of dates.
And its even more obvious that you didn't take the time to read my
message carefully before replying.
I have over 2000 programs in my company, and all of them have been
using date fields in files for a long time. Some of them have been
doing this for 20 years.
I do not have a large staff, or a large enough budget to STOP AND
REWRITE EVERY SINGLE PROGRAM to use 01-01-0001 instead of *Zeros.
Thats my objection to not allowing zeros, NOT that I think its
impossible to accomplish the same things, but THAT IF THEY'D ALLOW
ZEROS, I WOULDNT HAVE TO REWRITE 2000 PROGRAMS!!
So you're telling me that its BETTER for me to re-write all of my
software (and introduce god knows how many bugs) just because
"IBM knows best" and won't allow zeros in date fields?
Gary Guthrie
>
> A week? I work a 40 hour week.
What's that? <g>
> I would have to average 1.2 minutes per program
> to change, debug, etc. in order to change 2000 programs in 40 hours.
I was talking change only, not debug. I was also talking only changing
the zero comparison. I also qualified this with given fairly well
written programs. If written well, much/most/all of the changes could
be automated, in which case you could get it done in a couple of hours.
That's rarely the case, though.
With respect to just over a minute to modify a program - I mentioned 1
to 2 weeks. That gives you a 2 1/2 minutes per program with a 40 hour
week! Again, with well written code, even if you don't automate the
process, this would be fairly easy to accomplish. I have systems that I
have done similar things with. Without automation I went through and
modified about 300 programs in half a day.
> On top of that, you're neglecting the fact that I'd have to change
> all the files as well, in some cases, a corresponding dspf, etc.
>
Nope, I was only talking about the RPG mods as qualified above.
> How about this one... you've been telling me that I should change
> my programs to use *LOVAL instead of *Zeros. "So you're telling me"
> that using 01-01-0001 to mean "date unknown" is a better standard
> than using Zeros to mean "unknown"????
No, that's not what I'm telling you. That's what earlier posts in the
thread were talking about as a "solution". We need to be very careful
here - I'm not talking about Y2K readiness - I was simply talking about
changing the references. I DON'T think this is a solution at all, but I
can see where somebody might be "forced" into doing things this way.
My opinion is that if there is not yet a date, there should be no record
written requiring a date. The database design would take care of this.
As an alternative, I would accept null values to indicate the lack of a
date. But, this is not what the original thread was about.
> Gary, if you're telling me that using a valid date to mean "unknown"
> is a better standard than using zeros, then I think that "you've got
> bigger problems than you discuss here"
>
Like I said, that ain't what I'm saying!
> I agree that NULL would be a better standard, but the difficulty of
> re-writing all of my programs to use NULL makes it impractical.
>
And, that's why I mentioned the other concerns.
> What good does Date data types do if I can't use them?
Uhm, other people can use them? I think the answer is, you can use
them. But, like anything else, it ain't free. The problem is that you
can't use them the way you want to use them. Again, poor design cannot
predicate the way things work - allowing zero dates, that is.
> IBM could easily have a system value or something to "allow zero dates" so
> that we could have the best of both worlds -- (sigh) I'm sure I'll
> get lots of mail telling me I'm wrong about THAT, too.
Sure they could, but that is at the expense of standardization and as
such makes for a poor decision, in my opinion. I don't agree that it is
the best of both worlds - I think of it as more of the same world -
perpetuating the poor design.
The things I have said are not intended as a personal affront. Remember
that in a public place such as this, there are those who have not yet
looked into using some of the "newer" AS/400 features - RPG/IV, date
data types, etc. The comments (I've seen them many times from many
sources) that the fact that date data types aren't useful due to
"perceived inadequacies" tend to serve as a disservice to those who
might be exploring their use. It is my opinion that they should be
exposed to the other side of the argument, because there are excellent
reasons to use data data types.
Anyhow, gotta run...
Gary Guthrie
spam* nkp@bigfoot.com NKP
Gary Guthrie <ggut...@airmail.net> wrote in article
<14913D0B488A0B5D.3134E0AF...@library-proxy.airnews.ne
t>...
> My opinion is that if there is not yet a date, there should be no record
> written requiring a date. The database design would take care of this.
> As an alternative, I would accept null values to indicate the lack of a
> date. But, this is not what the original thread was about.
>
But that could mean splitting a file into God knows how many files, each
with the same key and a date field. This would be the case in a file that
represent an object, which goes through many states, each being logged by
date and perhaps time. Not very nice design, if you ask me (but then again,
you didn't :-))
--
Nils Kilden~Pedersen
Dave Begley
In article
<14913D0B488A0B5D.3134E0AF...@library-proxy.airnew
s.net>, From Gary Guthrie <ggut...@airmail.net>, the following was
written: > Again, poor design cannot > predicate the way things work -
allowing zero dates, that is.
Why do you say a zero date is bad design. What about an insurance file
that has the date of a person's death? That would be empty until the
person died. And there many other examples where a zero date is valid.
!^NavFont02F01890008IGE1HH8A014A
Gary Guthrie
> Why do you say a zero date is bad design. What about an insurance file
> that has the date of a person's death? That would be empty until the
> person died. And there many other examples where a zero date is valid.
Because there is no zero date. Zero is a meaningful mathematical
construct. It is not, as so often thought of, the absence of something,
which in your case is what you are discussing. You are talking null,
not zero. There is a difference.
If I have an order file that contains quantity shipped (an obvious
numeric field), would you accept that storing the alpha value "A" to
indicate no quantity had been shipped is bad design?
Now, I know this is a ludicrous example, but it is really the same
thing. I know you (and zillions other), won't agree...
Gary
Gary Guthrie
> But that could mean splitting a file into God knows how many files, each
> with the same key and a date field.
That would be, quite likely, the appropriate database design. Though,
as I've said, I will accept NULL values as appropriate. This shields
you from normalizing the database, if there is a particular aversion to
normalization.
Gary Guthrie
Leonard McCoy
I agree, null should indicate the absence of a value, unknown value, etc.
As you were saying, "A" is not a number and zero is not a date, neither one
should be used to indicate unknown. I feel that even 01-01-0001 should not
be used to indicate unknown, because this is still a valid date, although
more than likely an invalid value in any field you would use for a date.
(ex. The birthday of an individual was not given: BIRTHDATE = 01-01-0001
(Boy, he really looks young for his age!!! I really don't think so!!!),
BIRTHDATE = *null (that's understandable))
--
Leonard McCoy
Gary Guthrie <ggut...@airmail.net> wrote in article
<906C2E658FFA3D19.718B3E6C...@library-proxy.airnews.ne
t>...
Kent Milligan
Access... - now that's a real robust reliable database.
So you're saying you don't want the AS/400 to do any
data validation on your field? So we should stop checking
if date values are valid, if character data is being entered
into packed fields, etc?
Null values are what should be used for unknown dates and
nulls are supported in ILE RPG.
In article <09970503132618.O...@sprynet.com>, dave_...@sprynet.com (Dave Begley) writes:
--
Kenton Milligan
DB2/400 Development
IBM Rochester-AS/400 Division
km...@vnet.ibm.com
GO HAWKEYES!!
(opinions stated are not necessarily those of my employer)
spam* nkp@bigfoot.com NKP
t>...
Do I sense a bit of sarcasm here?
I fail to see what that has got to do with normalization.
--
Nils Kilden~Pedersen
Leonard McCoy
I agree, you're in a tough spot with dates in that many programs and files
in 6,0. I don't think a numeric in a date field should be allowed though,
that would defeat the purpose of having a data type as a date. Much like
having a numeric data type that allows the letter "A", it would no longer
be a numeric data type. The date type is not a string of numbers, it MUST
be a valid date and 0 is not one.
--
Leonard McCoy
> But null values were never available (until very very recently).
>
> What happens too those of us who have over 2000 home-grown programs
> that use 6-digit dates that start out as 0's...
>
> I cant convert all these programs to RPG IV, and change the logic to
> use nulls instead of zeros in all these programs...
>
Gary Guthrie
> Do I sense a bit of sarcasm here?
> I fail to see what that has got to do with normalization.
No, there is no sarcasm.
It has everything to do with normalization. If the user has a date
field in a record and he writes that record without knowing the value
for the date (because there is no date yet), then normalization dictates
the information be elsewhere.
For instance, if I have an order that has not yet been shipped, I should
not be writing a record that contains ship date. Only when a shipment
has been made, should I write a record containing a ship date.
Gary
David Abramowitz
The quantity shipped field is very interesting.
One of my clients uses the "Date Shipped" field to determine
whether or not an order, has in fact been shipped. This is
important. If the order has not yet been shipped, it may be
canceled. If there is a value in the DATE field, the order may
not be canceled.
In this case (and many others), zeros are a valid value. IBM
should realize that in any given system, when any given record is
created, that all of the fields in that record may, or MAY NOT be
filled at the time of creation.
As long as these rules apply to *date fields, I will continue to
use (without problems) numeric fields.
--
David Abramowitz
Paul Nicolay
Hi Gary,
About the normalization of your model I don't agree, as the relation
between a shipping date and an order is one-to-one, I shouldn't know why I
should put the shipping information in a different file. Off course I you
look at the rules defined by CODD, you should do it this way, but that is
just theoretical. (and not that good for performance). My idea about that
is that I still need a one-to-many relation before I start doing things
like that (not looking at some exceptions).
About the data stuff I fully agree, even if it is probably not always that
clear for an RPG programmer to talk about NULL-values. The argument of
having a lot of work, so I don't use new technologies is bad. I also
don't see the point of the original poster, when he will change his date
fields from the current numeric presentation to real date-types, he will
have to change his code anyway... so what's the deal about NULL dates ?
Regards,
Paul
__________________
Gary Guthrie <ggut...@airmail.net> wrote in article
<9183B8807F86D7C0.F0904770...@library-proxy.airnews.ne
t>...
The contents of this message express only the sender's opinion.
This message does not necessarily reflect the policy or views of
my employer, Merck & Co., Inc. All responsibility for the statements
made in this Usenet posting resides solely and completely with the
sender.
Gary Guthrie
Greetings Paul,
With respect to RPG and NULL values - you mention what's the big deal
with using NULL values for dates if the originator is going to change to
date data type. As long as the release is R370, I don't see a big deal,
however it's not quite as "clean" at earlier releases.
I'm mildly surprised that there is still so much clamor about date data
types. They have been around quite a while and aren't really nearly as
problematic as much of what you can read leads you to believe!
I think we are in agreement on these points - or so it appears from your
comments.
With respect to normalization - I find it interesting that you disagree
with "my" normalization model and then go on to cite Codd's rules and
further state that these are theoretical models and within the theory
that "my" model is accurate. But, the fact is that Codd lays out the
rules of normalization, "my" model follows them, and therefore by
definition is normalization in practice.
You are not stating that this isn't normalization - you are stating that
you don't always abide by the strict rules of normalization. That's a
completely different animal. There are definitely considerations when
deciding on the level of normalization you should use. And, typically
performance, as you mention, is the key consideration.
In the case I cited, it is possible that this could become an issue, but
realistically for most situations it is highly unlikely that would be
the case.
The real point is, there is a better solution than carrying invalid data
in the fields, irrespective of how strongly entrenched our habits are.
Gary Guthrie
spam* nkp@bigfoot.com NKP
> Gary Guthrie <ggut...@airmail.net> wrote in article
>
<9183B8807F86D7C0.F0904770...@library-proxy.airnews.ne
> t>...
> > > Do I sense a bit of sarcasm here?
> > > I fail to see what that has got to do with normalization.
> >
> > No, there is no sarcasm.
> >
> > It has everything to do with normalization. If the user has a date
> > field in a record and he writes that record without knowing the value
> > for the date (because there is no date yet), then normalization
dictates
> > the information be elsewhere.
> >
> > For instance, if I have an order that has not yet been shipped, I
should
> > not be writing a record that contains ship date. Only when a shipment
> > has been made, should I write a record containing a ship date.
> >
> > Gary
It seems I'm a little rusty on normalization, but wouldn't what you suggest
be beyond 3NF/BCNF, which is too teoretical at the moment due to database
performance? It certainly can't be the rules up to and including BCNF,
since the ship date and the order is a 1-to-1 relation.
(BTW, your right about the zero values in date fields. I confused zero and
null values. Must have been sleeping).
--
Nils Kilden~Pedersen
Gary Guthrie
There is not necessarily a 1 to 1 relationship between an order and a
ship date. It could be 1 to many, many to 1, many to many, depending on
business practices.
That having been said, I think we're now beginning to discuss
normalization rather than the original topic. I'm not sure I'm up to
another discussion on normalization right now!
I will say, though, that if moving a ship date to its own record causes
performance problems, then it falls squarely in the category of the
issues surrounding the level of normalization that I previously
mentioned.
Gary
David Abramowitz
I do not know what type of system you are using, but in most
manufacturing centers, an order (or a piece of an order) may be
allotted, and confirmed, but not yet shipped.
The problem is compounded when the manufacturer has multiple
distribution centers, and the order is split. That is part of the
order will ship from one center, and part of the order will ship
from another.
On a single order, it is then imperative to determine what has
shipped, and what hasn't. By having a file that contains the
components of the order, and a ship date within that file, the
user can immediately determine these facts.
--
David Abramowitz
Paul Nicolay
Hi David,
The described functionality requires a NULL field, not a date which is NULL
! Think about the NULL-value as a special constant like *LOVAL, *HIVAL
which doesn't math any other value, not even zero.
Another example would be the price that the order is selled for, it could
be NULL (it isn't sold yet), zero (it was for free) or any other number.
Based on this it should be clear that "zero" is not equal to "not used".
Kind regards,
Paul
________________
David Abramowitz <10544...@CompuServe.COM> wrote in article
<5nohkl$rgn$1...@mhafn.production.compuserve.com>...
CVWD
In article <5nohkl$rgn$1...@mhafn.production.compuserve.com>, David
Abramowitz <10544...@CompuServe.COM> writes:
>One of my clients uses the "Date Shipped" field to determine
>whether or not an order, has in fact been shipped. This is
>important. If the order has not yet been shipped, it may be
>canceled. If there is a value in the DATE field, the order may
>not be canceled.
>
>In this case (and many others), zeros are a valid value.
No. Zero is not a valid date and therefore not a valid value in a date
field. NULL is the valid value of a field that contains no data. IBM
should realize that and allow nulls in date fields.
Lance.
Dave Dunfield
CVWD <cv...@aol.com> wrote in article
<19970617152...@ladder02.news.aol.com>...
What is IBM's Definition of a null date?- V3R7 you still have to specify a
date value to return for a null. Guess what - you have to hard code
programs to recognize the date returned as your null date.
Bad implementation, zero would have been better.
Francis Lapeyre
CVWD wrote:
> In article <5nohkl$rgn$1...@mhafn.production.compuserve.com>, David
> Abramowitz <10544...@CompuServe.COM> writes:
>
> >One of my clients uses the "Date Shipped" field to determine
> >whether or not an order, has in fact been shipped. This is
> >important. If the order has not yet been shipped, it may be
> >canceled. If there is a value in the DATE field, the order may
> >not be canceled.
> >
> >In this case (and many others), zeros are a valid value.
>
> No. Zero is not a valid date and therefore not a valid value in a
> date
> field. NULL is the valid value of a field that contains no data. IBM
>
> should realize that and allow nulls in date fields.
>
> Lance.
*NULL is valid in V3R7 for dates, but I think you still have to compile
with the compiler option ALWNULL(*YES),
--
Francis Lapeyre flap...@communique.net
----------------------------------------------------------
"The probability of life originating from accident is
comparable to the probability of the Unabridged Dictionary
resulting from an explosion in a printing factory."
Prof. Edwin Conklin
****** If you spam me, you will incur the wrath of your Internet **
****** Service Provider. I KNOW who you REALLY are!!***************
Opinions are my own. Otherwise, they are someone else's.
The TEAMIBM Network
> No. Zero is not a valid date and therefore not a valid value in a date
field. NULL is the valid value of a field that contains no data. IBM
should realize that and allow nulls in date fields. <
Huh?...that just the point that's being discussed ad nauseum. IBM *does*
support the NULL value in date type DB fields (and zero is not a valid
date value in line with industry standards).
ILE-RPG handles it OK, but there are limitations in how OPM-RPG handles
it. But the big issue is many people don't want to change their
existing logic which uses zero, but, they do want to move from numeric
type fields to date type fields (for other advantages).
Of course they can just change from Numeric(6) to Numeric(8) and keep
the current logic.
Rod Orr Melbourne
Ed Smith
Dave Dunfield wrote:
-snip-
> > No. Zero is not a valid date and therefore not a valid value in a date
> > field. NULL is the valid value of a field that contains no data. IBM
> > should realize that and allow nulls in date fields.
> >
> > Lance.
> >
> >
> What is IBM's Definition of a null date?- V3R7 you still have to specify a
> date value to return for a null. Guess what - you have to hard code
> programs to recognize the date returned as your null date.
I thought V3R7 let you set fields to null values.
>
> Bad implementation, zero would have been better.
Yup.
--
Edward R.Smith mailto:eds...@scvnet.com http://www.scvnet.com/~edsmith/
ATS Project Leader mailto:eds...@ibmuser.com http://www.ibmuser.com
SMUG Webmaster http://www.ibmuser.com/smug
No unsolicited e-mail thank you.
Gary Guthrie
> Another example would be the price that the order is selled for, it could
> be NULL (it isn't sold yet), zero (it was for free) or any other number.
>
> Based on this it should be clear that "zero" is not equal to "not used".
Paul,
That's a great example for trying to explain the difference twixt NULL
and ZERO.
Gary Guthrie