How to store and use a non-entered Birth Date

I have created an Employees database table with a Birthdate TIMESTAMP field (for maximum compatibility across SQL brands). I want to be able to set, display, edit and update this field, but also allow it to remain unentered.

The SQL database side is OK, it is the Xojo IDE field that is of most concern. The Date Picker must have an actual date as the default, and todays date as default would be incorrect. How can I tell if the date picker has been updated unless I choose a non-real birth date as default? BTW I am ignoring the HH:MM:SS fields.

I have tried:

  1. Setting the default date to 01-01-01, but my DatePicker doesn’t like dates before 1600.
  2. Setting the default date to 9999-01-01, but the RowSet returns this as date 1900-01-00, which doesn’t even exist and causes an exception “Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD”, even though it is!
  3. If I set the Birth Date as a String in the database with blank as unentered, what date do I default the Date Picker as?

Var tempDate As DateTime = DateTime.FromString("9999–01-01 00:00:00") 'gives an Exception

Why is 9999–01-01 00:00:00 an invalid DateTime?
Should I set the default date to 3000-01-01 or some other date in the past/future?

I can’t specifically help with Xojo out-of-the-box, but Einhugur’s plugin allow for “empty” entries:

The control supports “NULL” dates or what some would call no selection.

https://www.einhugur.com/Html/DateControl/index.html

Disclaimer: I am NOT affiliated with @Björn_Eiríksson just a long-time user of this plugins :wink:

1 Like

BTW this works OK:

Var tempDate As DateTime = New DateTime(9999, 1, 1, 0, 0, 0)

Thanks @Jeannot_Muller,

I do use the Einhugur DatePicker on Desktop and WebDatePicker for the Web (love them!), but wanted to keep the IDE objects out of the discussion as I wanted to solve it as a generic issue.

I didn’t realise the Einhugur DatePicker could handle empty dates — that’s cool!

1 Like

Why? It is the same as picking any other date as default. Today or 1900 why is one correct and the other not?

Do yo mean a default in the Database? Not a great idea, better if you set the default as NULL. In your code you must have an IF to check if is null and change your DatePicker to a state that indicates a “not set”

If the default date in the DatePicker is Today, and not adjusted by the typist, and the record is saved, then the customer later queries all employees born on/after this date, then it will show this employee as a false positive.

I already have a NULL checker when reading from the Database. The main issues are: what date should I set in the DatePicker when a New Employee record is created (or existing Employee edited with no set Birth Date) and, if the Birth Date has NOT been entered, what should go into the database?

Having the DatePicker default to a NULL date is a great solution, and works for desktop. For the web, I will try to set the DatePicker default to 9999-01-01 then, if still this date when saving, NOT update the BirthDate field in the database (or set it to NULL).

Thank you Ivan

I still don’t know why DateTime.FromString(“9999–01-01 00:00:00”) fails.

1 Like

the datetime picker should support nil
it is possible to use
DateTimePicker1.SelectedDate = Nil
but the display remain …
having a non selected date is common.

Another example from one of my business apps. It is an application to manage all the needs of refugees in Germany for an NPO (coordinating help they need, like language training, seeking jobs etc.). Of course, the first step is to record the “meta data” of a new client. Sometimes the birthdate is not yet known, or it is questionable etc. Obviously, we can store 1901-01-01 as a birthdate in these cases and we can ignore those dates in any SQL SELECT for reports, And we can use that data to show the end-user in the UI, that the birthday ist still NULL, but therefore you need a date picker accepting (to show) a NULL value. Of course, we could work with workarounds, not showing a date if NULL etc. but from an end-user point of view: they want to have it simple and easy.

Initially, we thought that every employee would understand to just use 1901-01-01 for unknown/unconfirmed birthdays. Guess, what happened? Once the end-user is seeing a birthday, it will not be questioned any longer, even if its value is 1901 … Some employees just used their own logic: for instance entering their own birthday as an indicator that the birthday is unknown, etc.

1 Like

i think this Null thing need a feature request with all our arguments.

But since these are employees we are talking about, today is not a valid birthdate, so you can check whether the selected date is today, and if so consider it to be null. So, IMO, the default date should be today, which is very common, and not some other random (and uncommon) date, and you can actually use that to get the funcitonality you want from the datepicker (no selection)

Julen

it should be the datetime which you gave the control property including nil.
maybe not considered by xojo or not tested, or not supported by this date picker.

I meant he can proceed as I explained with the current DatePicker.

I am not saying the control shouldn’t be improved.

Julen

2 Likes

The best solution seems to be:

  1. store the Birth Date as NULL in the database until it has been set
  2. Initialise the DatePicker to be NULL for new records
  3. When editing an existing record, check the existence of a NULL database value and, if so, set the DatePicker to be NULL, otherwise set the DatePicker to the database value
  4. Upon updating the record, check if the DatePicker is still NULL and, if so, don’t update the database, else update with the DatePicker value

This seems to keep the highest integrity without using strange dates. It should also help with searching, reporting and false-positives. The only remaining issue is reverting a date back to a NULL, after it has been set, but this is a separate issue.

2 Likes

You could solve that by a second button etc. telling the user “clear field” and writing back NULL to the database. At least in my experience that is happening quite frequently when customers have to fill in lots of data. They might realise for instance that they typed in the birthdate of the wrong person AND find out that they are missing a birthdate for the current person … and in seconds you have a change request and an angry customer ;-).

It is hard to explain to a customer that there you have not thought about a possibility to clear the field. And if you don’t offer an option to clear the field, the data quality will evaporate in seconds, no end-user will then clearly care and just be frustrated and move on.

Use your own texfield subclass for handle date fields, setting date as a property and adjusting the text to date value. This way you can force text to 00/00/0000 or whatever you want when date is nil.

It’s a bit tricky as you have to control lots of things (selection of month/days/years, arrows, etc) but when done, works like a charm.

For field to database I use an intermediate DB object class that simply set the value as ‘NULL’ when the object is datetype and the value is nil.

Good approach. There are many workarounds, but I think what we all agree upon, is that a dateControl should accept an initial empty value and include the possibility to clear the field, once a date was entered. Einhugur’s Plugin does that, Xojo’s control doesn’t. And as long as it doesn’t it is not usable in many cases.

Well, if the control is actually native, it’s up to the OS to add this functionality.
Not sure they’ll read this forum and do that…

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.