Date definitions in files

[Guest.Visitor]

My preference (not necessarily others) is to use a DATFMT. This allows 2 capabilities that reduce coding: #1: unable to save an invalid date in a Date data type -- so you don't have to check the date field when reading to ensure that the date is valid... #2: sorting/selection requires less coding -- without concern of DATFMT, the data is stored the same way, so coding to sort/select based from the date doesn't require any logic to conversion from MDY to YMD, etc. HTH - Lee.

[David Abramowitz]

While the date data type is preferred, it is not always possible. There are circumstances where the database contains dates that will be filled in at a later time. A date data type may allow nulls, but somewhere down the line this will cause a problem. If nulls are not used, and invalid date error inevitably occurs. In such cases I use YYYYMMDD numeric, and let the programming sort it out where necessary. Dave

[Guest.Visitor]

Because of variations in kowledge of null-capable fields in our staff, we use 0001-01-00 rather null-cabable fields. This allows us to use Date data types. As needed, within the applications, we check the value of the date field and make any needed modifications. Thank you - Lee.

[David Abramowitz]

That's a great technique, but I would not recommend it for a shop with many older programs. The amound of time and effort needed to change pervasive tests for zeros in a date field would be prohibitive unless time, resources, and budget were available. OTOH, I'm going to keep it mind for newer systems. Dave

[Guest.Visitor]

You are very correct David. The files that we use '0001-01-01' as "null" are used in new system applications -- completely new functionality. When treating '0001-01-01' as null, any programming that uses this value for a date checking (aka expiration date) requires additional coding to test for '0001-01-01' and substitute *HIVAL to allow accurate date range processing. Thank you - Lee.

[Guest.Visitor]

Because of variations in kowledge of null-capable fields in our staff, we use 0001-01-00 rather null-cabable fields.

Checking for a null value is no harder than checking for 0001-01-00. Why not just train the staff?

[Guest.Visitor]

There is lies the issue DBThomas, variances in knowledge between individuals - techniques that are only known by a minority in the staff cannot be used or others may not be able to maintain the program when needed. Because of time, we haven't had available time for training and continued support of new and change requests. Good thought, though.

[Guest.Visitor]

Yarrington Lee said: "Because of time, we haven't had available time for training and continued support of new and change requests." Saying there's no time to train programmers is equivalent to saying you don't have time to buy gas for your car. Eventually that will come back to bite you. chuck Opinions expressed are not necessarily those of my employer.

[gary.shipp@s3t.co.uk]

What is the general approach taken to defining dates in files? Should they be held as numerics of appropriate length and then manipulated within the application, or, safe is the wrong word to use, but ok to define them as DATFMT within the physical file? We do have issues with legacy applications that make our decision more difficult, but were considering making the jump to date format fields in new projects. Is this common practise or not? Thanks

[Guest.Visitor]

Those that choose to spend few/no resources during the short-term will pay more in the long-term to accomplish goals. Either the in-house staff will not be able to accomplish tasks accurately and efficiently, requiring high$$ consultants to complete the work, or tasks will be filled with inaccurate processes due to staff having to learn "on-the-fly." Thanks - Lee.