AnsweredAssumed Answered

Stored, Read and Reference a Date Variable for Data Warehouse Incremental Load

Question asked by AaronChandlerworth5261 on Feb 11, 2014
Latest reply on Feb 13, 2014 by AaronChandlerworth5261
Purpose::  Incremental Load for a Data Warehouse  staging table from SalesForce

I have a database stored proc that returns a single date value (the last successful execution datetime).  I need to be able to read, store and then reference that variable within a process in order to use it as the filter in a salesforce query.  

My salesforce query has a where statement like 'LastModifiedDate >= [MyVariable]'

I've been using a connector to read my stored proc.
I use a Set Properties to assign the result to a dynamic process property
I reference the dynamic process property in the parameter of my salesforce connect that reads from a table.

Errors every time. I can't get the date formats to match.  

SQL is returning one formation but salesforce needs another and I don't see any available operations for converting.

If I convert in SQL and return a convert(nvarchar,datetime,126), then I need to configure the return value as a character since boomi doesn't accept it as a datetime.

And if I leave it as a date time, salesforce complanes that it's the wrong format.

Has anyone encountered this or have an alternate solution?


Outcomes