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:
Setting the default date to 01-01-01, but my DatePicker doesn’t like dates before 1600.
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!
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?
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.
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.
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)
store the Birth Date as NULL in the database until it has been set
Initialise the DatePicker to be NULL for new records
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
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.
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.
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.