If you’ve worked with Ninox date fields chances are you’ve noticed that they don’t always work as you might expect. If you haven’t read David’s article titled “Do As Server – Chapter 2″ in the October 27th issue of our newsletter I highly recommend you check it out here as this article expands on some of the stuff that was mentioned there.
Here are the biggest things that tripped me up about date fields when developing Calendar Plus. You probably know about some of them already. A lot of these things are equally applicable to date-time and appointment fields, which are both stored in the same way. I don’t have the space to provide solutions to these issues here, but my hope is that a knowledge of what is going on with dates behind the scenes will help you work around the weirdness!
As an example, if I set a date field to December 10th 2020 here in New York, David looks at it in Dallas Texas (1 hour behind) and sees December 9th 2020. If he were to add a formula field with the code:
format('Date Field', "YYYY-MM-DD hh:mma");
He would see: 2020-12-09 11:00pm. The timestamp was set to midnight on the 10th New York time, and it has been read in his local time (1 hour behind), and displayed without the time component. AH!
This is explained in David’s article linked to above. As an example of this, if I in NYC place a button on my record with the following code:
do as server 'Date Field' := date(2020, 12, 10) end;
and click the button, I will see the date field change to 12/09/2020. This is because my database is on the public cloud, and the code is being executed in Germany. The situation is the same as if someone has gone into my database in Germany and changed the date to the 12th, resulting in the same problem we saw in number 1. David shows a neat way to address this using the format() function in his article linked to above.
This is something that is very useful to know given the above information. As an example of how this affects dates, let’s say I insert the following code into a trigger after update function on ‘Date Field’:
'Date Field' := date('Date Field')
then I set the date field to 12/10/2020. What do you think will happen? This is a bit of a tricky one to think through, but the date is immediately changed to 12/09/2020. Any date that I choose gets changed to the previous day. Here’s why:
In general if you are manipulating dates in script you need to know exactly where your code is being run. Trigger after update and Trigger on create functions are always run on the server, and there is no way around it. On click and formula functions are run locally (on your computer) unless you specify “do as server”. Global functions are run wherever you call them, unless the global function itself includes “do as server”.
I’ve seen this come up in a couple of circumstances. A big one is when adding a day or days to a date. Let’s say we have this code in a button:
let firstDate := date(2020, 10, 1); let secondDate := date(2020, 11, 1); alert(text(firstDate + 1) + " " + text(secondDate + 1));
When we click the button we see an alert that reads “10/02/2020 11/01/2020”. A day was added to the first date, but it doesn’t look like the second one was affected! This is because of the time shift on November 1st 2020 in New York. The results may be different where you are, depending on when your time shift occurs. By changing date + 1 to date(date + 1.5) this issue can be avoided.