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?
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!
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.
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.
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)
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.
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.
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.
