Parts of a date unknown

[shalom]

I'm new to openoffice and plan to set up a database for an awards program for a girls club. Sometime I know the complete date, but sometimes I only know the month and day or month and year. How would I handle that?

[RoryOF]

In that case, I think your dates have to be text strings. If you need to calculate from the dates, then you will have to convert full dates to their numeric values and adopt a convention for partial dates, such as that for thoroughbred horses (1st January in Northern Hemisphere, 1st August in Southern hemisphere).

[shalom]

Thank you. I was afraid I would have to do it that way.

[Villeroy]

Anything is better than strings because strings are difficult to evaluate.
Enter some date together with a boolean value if the date is sure or unsure.
Or use 3 numbers for the year, month and day.

Looking for a birthday in September in a string field, you search for substring "Sep" or "September" or "sep", "september", "SEPT" or "9" (but not "1999") or "09" (but only if the number refers to the month rather than day "9"), ... it is hopeless. WIth a clear date or 3 numbers representing a date, you can get anything.

[shalom]

Hmmm, that gave me an interesting thought. Maybe I could figure out how to do a drop down or some such field for each part of the date. The month would have a range of 1 to 12, day 1 to 31 and figure out how to handle the year. Then I could manipulate it and put it in a text field for printing, etc.

[RusselB]

One potential problem with doing the drop-downs like that, is that you could (probably accidentally) end up with an invalid date... eg: 2-30 (Feb. 30)
There are ways to prevent that in Calc, and I'm going to guess it can be handled in Base, but I don't know how.

[shalom]

I just had another thought. Maybe I could estimate the missing part(s) and have another field to note what portions of the date field are estimated, then perhaps have it display/print in a different color. Is it possible to set the color of text in a field with code? If, so how would I do it?

[UnklDonald418]

I believe Villeroy's suggestion about separate integer fields for each component of the date would likely be the best solution. I suppose you could use NULL to indicate an unknown value in those fields, but that could add complexity to any queries so it would be better to have a NOT NULL constraint for those fields then a 0 could indicate an unknown value.
Text fields (or any numeric type) in a HSQL database do not store any formatting information so any color changes would need to be handled at the form level.

[Villeroy]

The following adds 3 optional columns for year, month and day plus constraints. Change the "TableName" to your actual name and modify the year numbers of the "Valid_Year" constraint to your liking.
menu:Tools>SQL...

Code: Select all

ALTER TABLE "TableName" ADD COLUMN "Y" SMALLINT NULL;
ALTER TABLE "TableName" ADD COLUMN "M" TINYINT NULL;
ALTER TABLE "TableName" ADD COLUMN "D" TINYINT NULL;
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Year" CHECK ("Y" BETWEEN 1900 AND 2030);
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Month" CHECK ("M" BETWEEN 1 AND 12);
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Day" CHECK ("D" BETWEEN 1 AND 31);

Finally, menu:View>Refresh Tables updates the Base GUI to show and handle the modified table.
Notice that there is nothing in Base which allows you to add CHECK constraints. This has to be done behind the scenes talking directly to the underlying database which is documented as a stand-alone product on this page: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Since every comparison with a NULL value results in NULL, SELECT * FROM "TableName" WHERE "M"=1 AND "Y"<2000 ignores all NULL values in "M" and "Y".
SELECT * FROM "TableName" WHERE ("M"=1 OR "M" IS NULL) AND ("Y"<2000 OR "Y" IS NULL) includes missing "M" and "Y".
SELECT * FROM "TableName" WHERE NOT "D" IS NULL AND NOT "M" IS NULL AND NOT "Y" IS NULL queries all rows with complete dates.
SELECT *, CAST("Y" || '-' || "M" || '-' "D" AS DATE) AS "Date" FROM "TableName" WHERE NOT "D" IS NULL AND NOT "M" IS NULL AND NOT "Y" IS NULL queries all rows with complete dates and adds a calculated date field for all these values.
SELECT COALESCE("D",0) AS "ZD", COALESCE("M",0) AS "ZM", COALESCE("Y",0) AS "ZY", <other columns> FROM "TableName" replaces missing date numbers with zero.

I suppose you could use NULL to indicate an unknown value in those fields, but that could add complexity to any queries

Following Uncle Donald's advice adding the constraints:

Code: Select all

ALTER TABLE "TableName" ADD COLUMN "Y" SMALLINT DEFAULT 0 NOT NULL;
ALTER TABLE "TableName" ADD COLUMN "M" TINYINT DEFAULT 0 NOT NULL ;
ALTER TABLE "TableName" ADD COLUMN "D" TINYINT DEFAULT 0 NOT NULL;
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Year" Check ("Y" = 0 OR "Y" BETWEEN 1900 AND 2030);
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Month" Check ("M" BETWEEN 0 AND 12);
ALTER TABLE "TableName" ADD CONSTRAINT "Valid_Day" Check ("D" BETWEEN 0 AND 31);

This does not allow any NULL values. If you leave out any y/m/d number in a new record, a zero will be inserted by default. A row selection where "M"<=9 would include the zeroes. WHERE "M"=9 would not include rows with missing months. WHERE ("M"=9 OR "M"=0) would include them.
SELECT * FROM "TableName" WHERE "D" > 0 AND "M" > 0 AND "Y" > 0 queries all rows with complete dates (which is a little bit shorter indeed).

Notice that both approaches with separate y/m/d numbers allow invalid values such as 31st of February. It would be rather complicated to avoid this.

[RoryOF]

The reason I suggest a text string/field was that if some dates were partial, there ought not be any valid date calculations. Personally, I like the horse solution!

[Villeroy]

Yes, if the info about the date is for human eyes only, text is OK. For machines clear discrete numbers are more distinguishable.

[shalom]

Is there a type of case statement that could be used to set a variable to 30, 31 or 28 for the end of the month. There would still be the issue of leap year, but all the other months would be correct.

As far as coloring the date, it would be acceptable to do it on the form and on the report to indicate that a portion of the date is not accurate. This would allow me to sort based on the date so awards earned in a specific year (in this case the day is unknown) could be grouped together. If I choose to go this route, what command(s) would I use in the form and the report?

[Villeroy]

http://www.hsqldb.org/doc/1.8/guide/ch09.html

Code: Select all

SELECT "Y","M","D","ID" FROM "TableName" 
WHERE ( "M" IN ( 4, 6, 9, 11 ) AND "D" > 30 )
  OR ( "M" = 2 AND "D" > 28 AND ( MOD( "Y", 4 ) > 0 OR MOD( "Y", 400 ) > 0 ) )
  OR ( "M" = 2 AND "D" > 29 AND MOD( "Y", 4 ) = 0 )

[shalom]

That's good and will be useful for picking the individual clubs in my app as well. Thank you very much.

I just thought of another way to do it. Click on a date button which opens a popup where the date is picked like normal. Then if a part is incorrect, it is flagged in another field. That field is used to extract the correct parts to display in a text field.

[Villeroy]

A date picker would never return an invalid date. However, you can not implement user interface gimmicks without massive time ánd effort and a high level of programming skills.
In a similar situation, I use a similar query like the above to select inconsistent data and display them in a little subform at the bottom right corner of a form. If all the data are consistent, the subform grid is a blank space, otherwise inconsistent records become visible and you can edit them in place. This is implemented within minutes and good enough to be useful.
The CHECK constraints avoid most of the possible inconsistencies and the rest is shown in some GUI section where you can fix them.