Jump to content
  • 0

Get System Date/Time and use in Table Trigger upon insert




Does anyone know how to get and use the system date/time (the local computer's date/time and not a UTC value) in a table's triggered actions, and then use it? I have a table that logs activity from other tables and need to be able to log the user's local system date/time upon insert. The function SysDateTime() isn't available in a table's formula field and I'm not sure how/if I can use SysDateTime() to populate a date/time field in the table upon insert. 

In the Activity table's Upon Insert trigger, it would be something like a date/time field: Created_Local=SysDateTime() in the trigger's Set section (Update #inserted).  I need to store both the local system date AND the time in this field. 

I can't use a UTC date (the normal TimeStamp method) because my users are all over the globe and the date/time wouldn't make sense to them without calculations. I need to use the local computer's system date.

I also don't want to have to create a new field in each table that does an 'Insert Into' the Activity table upon their own Insert Data as there are many of these tables and I'd have to put the local system date/time field in a lot of submission pages (to be able to use SysDateTime() since it's not available in a formula field). So to me the simplest solution is to somehow update Activity table's field "Created_Local" with SysDateTime() upon insert of new data. Any help would be greatly appreciated!

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

If I got your inquiry correct, what you wanted to do is to capture the actual timezone (or time) of the user instead of relying on the trigger. The trigger wont be able to know that since it is on the backend. 

If you are using a datapage (preferably a form type such as submission form or Details page) to insert or update the data, perhaps you can add a date/time field and use that on your form as a calculated value. 

Once it is now a calculated value, the sql SysDateTime() should now work and be included on the records where you can no use them on the trigger.


Link to comment
Share on other sites

  • 0

Hi NiceDuck,

Sorry about the late reply--for some reason I haven't been getting notifications when an thread is updated. 

Thanks re the tips--I ended up having to use a separate field for the table to get the local computer's date/time, and then use that in the trigger. When I tried GetSysDate I often would get a UTC date that was not the same as my user's date (I have users in the U.S., Australia, and the U.K. so it's literally all over the map). 

I put some code in the footer to get the local date/time, if anyone needs it: 


 /* Declaration and initialization */

 Stamp = new Date();

 var v_TimeStamp;

 Hours = Stamp.getHours()

 Mins = Stamp.getMinutes();

 /* Attach a prefix with digit '0' if the minutes is less than 10. */

 if (Mins < 10) {

   Mins = "0" + Mins;


 /* Construct the value of the v_TimeStamp variable in the format m/d/yyyy hh:mm */

 v_TimeStamp=('' + (Stamp.getMonth() + 1) +"/"+Stamp.getDate() + "/"+Stamp.getFullYear() + ' ' + Hours + ":" + Mins);




Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...