Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Working with Dates in Integration Manager VBScript

Working with Dates in Integration Manager VBScript

  • Comments 3

David Meego - Click for blog homepageToday I worked with my colleague Rob on an Integration Manager for Microsoft Dynamics GP issue.

We had a customer who was importing some General Ledger journals into their system and wished for the journals to be set up as reversing journals with the Reversing Date set up as the first date of the next month. The plan was to use the following script to manipulate the Transaction Date to create the Reversing Date.

Field ScriptText for Reversing Date

'  Added by the IM Script Library
'  Category: Microsoft Dynamics GP Scripts
'  Script Type: field Script

dtTrxDate = DestinationFields("Transaction Date")

'Add one month to the transaction date. This will handle short months correctly.  For example, adding 1
'month to January 31st will result in February 28th (or the 29th if it’s a leap year).
dtDate = DateAdd("m", 1, dtTrxDate)

'Set the new date to the first of the month following the transaction date
dtDate = DateSerial(Year(dtDate), Month(dtDate), 1)

This is the code that the customer had in their system. It did not cause any errors, but the Reversing Date was being set to the current system date.

While the code looks fine, I noticed that there was nothing in the code to "return" the dtDate variable to the Reversing Date field. Adding the following line to the code resolved this issue:

CurrentField.Value = dtDate

 

However, when we run the integration, the script failed on the above line saying it was unable convert the value "01/06/2012 12:00:00 a.m." to a date.

When I saw this a little alarm bell went off at the back of my head. I noticed something that was a (little) known issue.....

The time suffix with periods/dots/full stops is not understood by SQL Server when converting strings to datetime variables. This is discussed in the blog post: Quick Tip: Passing Date and Time Variables to SQL Server.

After getting the customer to change their Time regional setting back to AM & PM rather than a.m. & p.m., the integration progressed, but gave us the following error:

DOC 1 ERROR: The reversing date must be the same as or later than the transaction date.

 

This then made me think that Integration Manager was trying to interpret "01/06/2012 12:00:00 AM" as January 6th 2012 rather than the correct 1st June 2012. To test we temporarily changed their Date regional setting to MM/DD/YYYY and surprise, surprise.... the integration worked.

So, we restored the Date regional setting back and added code to convert the date into the required US format (MM/DD/YYYY).

Updated Field ScriptText for Reversing Date

'  Added by the IM Script Library
'  Category: Microsoft Dynamics GP Scripts
'  Script Type: field Script

dtTrxDate = DestinationFields("Transaction Date")

'Add one month to the transaction date. This will handle short months correctly.  For example, adding 1
'month to January 31st will result in February 28th (or the 29th if it’s a leap year).
dtDate = DateAdd("m", 1, dtTrxDate)

'Set the new date to the first of the month following the transaction date
dtDate = DateSerial(Year(dtDate), Month(dtDate), 1)

sDay = Right("00" + Trim(Day(dtDate)), 2)
sMonth = Right("00" + Trim(Month(dtDate)), 2)
sYear = Right("0000" + Trim(Year(dtDate)), 4)
sDate = sMonth + "/" + sDay + "/" + sYear

CurrentField.Value = sDate

Now the integration worked, regardless of the regional setting value. As the time component was no longer included in the script, the issue with 12 hour time suffixes also would not occur.

 

However, as the 12 hour time suffix issue can cause errors (below) elsewhere, we still suggested that this be changed system wide.

/*  Date: 12/15/2011  Time: 10:44:04
SQLSTATE:(22008) Native Err:(241) stmt(99398048):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.*/

 

So please be aware that when working with dates in Integration Manager you will need to ensure the resulting values are in US date format.

For more information on supported date formats, see Mariano's post at Supported Date Formats in Integration Manager.

David

  • Hi David,

    I did some work for a client a few weeks ago.

    Receivables and standard journals seem quite happy with UK date formats but the reversing date on a reversing journal and Payables Transactions want US or Japanese (yyyy-mm-dd) formats.

    IM wouldn't be so bad if it was at least consistent across the board with how it handles dates.

    Ian Grieve

    p.s. the other problem to watch for when using VBA is a blank line at the very top of the script which causes it to break and stop you reopening the script to fix.

  • Cool! Good to know.

  • Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/.../working-with-dates-in-integration.html

Page 1 of 1 (3 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post