Sign in Subscribe
Microsoft Dynamics GP

Dynamics GP will not remove hold or why timestamp format in GP matters

Tim Wappat

• 3 min read

If Dynamics GP will not let a SOP module, transaction process hold be removed in the user interface, it may be due to formatting of time stamps. Check no integrating applications have created that hold incorrectly.

The issue is that GP is real fussy about formats of timestamps, one place time stamps can cause problems is in use with the SOP Transaction Hold table, the following query illustrates what you need to know about this.

SELECT [PRCHLDID], [HOLDDATE], [TIME1]

This query gives us some data to look at, that is if some holds exist for that document supplied to the SQL query...

GP Timestamp column

The first thing to notice is that GP often splits/spreads the Time and the Date components of the point in time over two fields, in this case the point in time is represented by HOLDDATE and TIME1. These two fields are of the sql data type "datetime", something that can catch out people when new to GP databases as it may not be totally obvious what is going on.

Lets look at the first row, it is for the hold type "CREDIT" and represents the point in time, 3rd of May 2020 at eleven minutes past three and 31 seconds.

  • It is interesting and important to note that for the HOLDDATE field, it has a date but see how the date has zeros for all the time elements of that HOLDDATE datetime value. To fill up the unused time component, the time has been defaulted to midnight. A default time of zero (midnight) is used, as the unused half of the datetime sql data type may not be set to null (we need all those bytes).
  • Now also note how the TIME1 field value has a time component, but the date has been defaulted to a "default" of the 1st of January 1900. This date by convention is used to fill the date component of the time, as the unused half of a datetime data type may not null.
  • Further, it is vital to note that the milliseconds of the time has been zeroed too.

Now if you disobey these format rules, the GP user interface will do weird things. More often than not, it just doesn't do anything, that can be really perplexing as a problem solve because the records to the untrained eye look fine and similar to other records right next to them. It may be as simple as including milliseconds (not zeroing them) that can prevent a user from removing a hold in the GP user interface!

These time stamps are used in this way for many tables, where the same rules apply. It is very important to provide a correctly formatted time and date field if integrating or manually scripting against the GP database. This is a really common rookie mistake for developers working with GP, its easy to get lazy or not notice the formatting to be wrong. It can also change behaviour and totals, as time span calculations may not result in the same outcome if time components are left in date fields etc. Imagine the impact when totalling values over time spans, urgh.

So to save some head scratching, and to make a GP compatible timestamp for the current time field in .NET use the following code snippet:

DateTime now = new DateTime(1900, 1, 1).Add(DateTime.Now.TimeOfDay);
DateTime ForFieldUse= now - new TimeSpan(0, 0, 0, 0, now.TimeOfDay.Milliseconds);

Don't be tempted to simplify this and try to access the .now in two places on the same statement as you may find the times have shifted enough between calls to cause the milliseconds not to zero correctly as two different values would be returned and be subtracted, not resulting in zero! Date times are immutable so you can't simply zero the milliseconds component either.

To get the date only value for use in GP date fields, you may simply use the in build .NET method ".Today" as shown in this snippet:

DateTime.Today;

Hopefully this will help get back up and running after Googling the problem you've just encountered! - If so do comment, it keeps me motivated to blog more.