Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Salesforce Origin incorrectly converting date into datetime

I have configured a Salesforce stage to read from the Salesforce Account object. The Account.LastActivityDate field has a data type of date, not datetime. When retrieved by the stage it appears to have been converted into a datetime (with the incorrect time). For example, if the value in Salesforce is 10/21/2019 the record in StreamSets has a value of 10/20/6:00:00 PM. It is consistently 6 hours behind.

For example, if the Salesforce field value is 9/14/2017 then the SDC Field value: LastActivityDate=Field[DATETIME:Wed Sep 13 18:00:00 MDT 2017] (MDT is our timezone)

When the value is inserted into the database I see: 9/13/2017 6:00:00 PM.

Is this a bug in the stage? In the JDBC Producer I found I can use something like: "paramValue": "SUBSTR(cast(? as DATE) + 1,0,10)" to convert the date strings, but it seems like a hack to me and dependendant upon the system timezone.

Salesforce Origin incorrectly converting date into datetime

I have configured a Salesforce stage to read from the Salesforce Account object. The Account.LastActivityDate field has a data type of date, not datetime. When retrieved by the stage it appears to have been converted into a datetime (with the incorrect time). For example, if the value in Salesforce is 10/21/2019 the record in StreamSets has a value of 10/20/6:00:00 PM. It is consistently 6 hours behind.

For example, if the Salesforce field value is 9/14/2017 then the SDC Field value: LastActivityDate=Field[DATETIME:Wed Sep 13 18:00:00 MDT 2017] (MDT is our timezone)

When the value is inserted into the database I see: 9/13/2017 6:00:00 PM.

Is this a bug in the stage? In the JDBC Producer I found I can use something like: "paramValue": "SUBSTR(cast(? as DATE) + 1,0,10)" to convert the date strings, but it seems like a hack to me and dependendant upon the system timezone.