Execute SQL Statement - Date Issue

SOLVED

HI

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?

Parents
  • +1
    verified answer

    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)

Reply
  • +1
    verified answer

    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)

Children