Execute SQL Statement - Date Issue


i have the following execute sql statement action running on a opportunity workflow rule:

Insert into fundlink (flnk_fundid, flnk_personid, flnk_opportunityid, flnk_invamt, flnk_invdate)

values (#oppo_fundid#, #oppo_primarypersonid#, #oppo_opportunityid#, REPLACE('#oppo_forecast#' , ',', ''), '#oppo_closed#');

all works ok apart from the date that its setting - e.g the closed date on the opp is 09/11/2021 but when this rule is running its setting the flnk_invdate field to 11/09/2021

any idea?

  • This will be due to somewhere a system is picking US date format which with that date is valid either way around. If you went to something like the 13/11/2021 it will either fail or just set it correctly. When dates are ambiguous these things can happen. Is the date value getting set by a default value on the table field? That insert doesn't insert anything into the field, so where is it getting set?

  • Personally I would create a SQL Stored Procedure to do the most part of this (e.g. named 'OppoFundLink') and then just called it on the Execute SQL (e.g. EXEC OppoFundLink #oppo_opportunityid#) 

    Within the SQL Stored Procedure I'd then gather all the information from the Opportunity table directly using the OppoID passed into it and handle the Insert as well, useful if you're doing inserts on a system that had previously existed before 7.2 and thus still using the SQL Identities table to get the next ID for the record (even though it has since been upgraded to a later version)

  • thanks Matthew - that worked!