I am having issues with blank dates submitted via a custom form. Sometimes an end user will leave a date field blank on the form and submit the form to the database . The value in the database column then is "0000-00-00" and is type "date."
I had to write a small function to output a blank date as " " to PDF or when viewing the data from view.php. For some reason, blank date values from the table of a custom form I created will show as "12-31-1969", "00-00-0000", "11-30–0001" on view.php or the created PDF if I do not use the CheckBlankDate () function I created.
I am puzzled. Does anyone know why a blank date of "0000-00-00" in the database will display in my form (view.php) or PDF as one of these three "12-31-1969", "00-00-0000", "11-30–0001"?
-Mike
//Check for Blank Date m-d-Y
function CheckBlankDate($DateString)
{
Mysql date field is being treated as not null (meaning if not specified setting to 0 or 0000-00-00). So instead of an empty string ("") you get 0000-00-00.
Your system or php is using a unix timestamp meaning it calculates and expresses time as the number of seconds since Midnight January 1, 1970 UTC (known as the UNIX epoch or birth of unix). Most computers express this as December 31, 1969 as many of us are located a few hours behind the UTC time zone. So unspecified dates often get expressed in php as 12-31-1969.
11-30–0001 - that I don’t know maybe someone else can help with that.
Also since the maximum number of seconds (unix timestamp) that a 32 bit processor can hold as a variable is 2147483648 that means OpenEMR will not function correctly (at least in regard to dates) in the year 2028 for those who are running from a server with a 32 bit processor (or possibly running a 32 bit operating system on a 64 bit processor like many windows systems today).
Servers running 64bit operating systems have a little more time before they will need an upgrade (about 4 Trillion years - give or take a few billion).
Thanks for the explanation. I was puzzled as to why the UNIX time stamp was displaying in my "echo" statements when in fact the DB value was 0000-00-00.
1.) Do I need to change the attribute settings of the date field in phpMyAdmin( currently using 3.1.3.2)? Or the time stamp settings of my system ( SUSE Linux Enterprise Server 10 SP2)?
I am looking at the structure and noticed that I have a checkbox for "Null" that is checked and also for "Default" there is a dropdown box which has "NULL" selected (the other choices are "None", "As defined", "CURRNT_TIME_STAMP".) I used formscript.pl to create the tables for custom forms and did not touch any of the default structure.
2.) What is the proper way to have a blank date display when using php ( "echo" statements)? Or is it necessary to write a custom function for these cases?
Seth did a great job explaining the trouble. I’ll try to answer your two specific points and I hope that others chime in too.
1) You could change the ‘null’ option for all your date-time fields in the database. This would allow a date-time field to have a null value. Otherwise it will automatically populate with 00-00-0000 if it does not get a valid date-time value.
2) There is no specific way to have a blank date field other than just making the text-box empty. Sometimes it makes sense to have the data in the text-box default to a specific date, such as the current date. Otherwise I suggest leaving the box empty. You could write some custom code yourself that screens out data loaded from the database. For example, if you don’t want people to see ‘00-00-0000’ dates then do a check and alter the value before you ‘echo’ it to the display. Here’s a pseudo-code snippet:
if ($mydate == ‘00-00-0000’) { $mydate = “”; }
echo $mydate;
Thanks for your reply. I am a little confused with your reply to point 1 - “You could change the ‘null’ option for all your date-time fields in the database.”
In PhpMyAdmin I am looking at the structure. My date fields are type "date" not type "datetime" (not sure if this matters) and the "Null" setting is set to "Yes" and the "Default" column has "NULL" as the value. I thought these meant that the value is set to be NULL when a blank value is submitted to the database via the form submit.
I did create a custom script similar to your suggestion as that was the only way I could of think of to handle a blank field being displayed when I "echo" the value. I was just curious if there was a better way and wanted to see how others handle blank dates in OpenEMR forms.
With reference to the ‘null’ comment; I believed that setting the default value of a Date field to NULL would allow it to be null instead of ‘00-00-0000’. It seems that I may be wrong.
So it seems this has a blurry answer that can only be clarified by server settings, database configurations, and checking date values in the program code, in our case it’s PHP.
Thanks for the references and wealth of information!! This date field issue was driving me crazy. I did not have a good base starting point in dealing with a blank date when I first created my form. After discovering the issue I attempted to fix it. As I was cleaning it up in one place I created a mess in another. It was an endless cycle of frustration. I did not realize how complex dealing with a blank date in MySQL was going to be - I think I spent more time on this then actually coding the logic in my form - LOL!
I will also check my server settings. As far as database settings go, I have left everything as default ( initial openemr db installation as well as tables created by formscript.pl).
IMHO (this depends on the type of data being used) it would be best wherever possible to use a javascript function that doesn’t allow the user to submit the form if the date field is blank or improperly formatted.
There is some javascript date-format logic built into the DynArch Calendar. So as long as your form is using that code, then an improperly formatted date should be caught and the user notified.
But it doesn’t catch a blank date field. In some cases it’s OK for a date field to be blank, but in cases where it’s not then some javascript should be written to make sure the mandatory field is not blank. Then, rely upon the DynArch error checking to validate the string entered into the field.
Thanks again for your points. I am using the OpenEMR pop up calendar ( the one that is created for form use via formscript.pl). In my case I do have instances where the date field is not mandatory.
If I format a blank date on the client side before the form is submitted, what should I use as a "placeholder" for a blank date to be inserted into the OpenEMR table?
Otherwise, I am currently using the function below when I "echo" my blank dates.
Hey Mike, Stick what works for you. I think you’ve got it right. The formscript.pl code creates a set of files that uses the DYnArch calendar code. So you’re all set there.