Working with Dates in Integration Manager VBScript
Today 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