Date has always been one of the most complex functions to be used in integration. DBSync has in-built date function that can be used in the mappings to convert date from source to target format.
Along with the in-built date function, you can also utilize the date functions of various databases to convert the date at query level.
Using MySQL DATE_FORMAT() function:
When we are reading from MySQL database, we can use MySQL functions in Reader query. We can use MySQL built-in Function DATE_FORMAT() to get the date in Salesforce format. For Example, take a table as shown below. Now we will query the table and get the output date in Salesforce format using DATE_FORMAT() function.
Table Name: DBAccount
AccountName | BillingStreet | AnnualRevenue | LastModifiedDate |
ABC | 123, Ginger Street | 50000.00 | 2010-03-15 00:00:00 |
Query:
SELECT AccountName, BillingStreet, AnnualRevenue, DATE_FORMAT(LastModifiedDate,'%Y-%m-%dT%H:%i:%sZ') AS 'LastModifiedDate' FROM DBAccount.
Result:
ABC | 123, Ginger Street | 50000.00 | 2010-03-15T00:00:00Z |
Using DBSync DATE() Function:
We can also use the Date function of DBSync to convert MySQL Date into Salesforce format. An Example is shown below:
TargetField = DATE (DATE (VALUE("Source_Field"),"yyyy-MM-dd hh:mm:ss") ,"yyyy-MM-dd'T'HH:mm:ss'Z'").
Using DBSync DATE() Function:
We can also use the Date function of DBSync to convert Salesforce Date into MySQL format. An Example is shown below:
TargetField = DATE(VALUE("Source_Field"),"yyyy-MM-dd hh:mm:ss").
Using DBSync LSPLIT() & RSPLIT() Functions:
We can use DBSync's LSPLIT() and RSPLIT() functions to convert a Date from Salesforce to MySQL Format. An Example is shown below:
TargetField = LSPLIT(LSPLIT(VALUE("CreatedDate"),"."),"T") + " "+RSPLIT(LSPLIT(VALUE("CreatedDate"),"."),"T").
Using DBSync DATE() Function:
We can also use the Date function of DBSync to convert MySQL Date into Salesforce format. An Example is shown below:
TargetField = DATE (DATE (VALUE("Source_Field"),"yyyy-MM-dd hh:mm:ss") ,"yyyy-MM-dd'T'HH:mm:ss'Z'").
Using SQL Server CONVERT() & CAST() functions:
When we are reading from SQL Server database in DBSync - while writing the Reader query - we can use MySQL built-in Functions CONVERT() and CAST() to get the date in Salesforce format. For Example, take a table as shown below. Now we will query the table and get the output date in Salesforce format using CONVERT() function.
Table Name: DBAccount:
AccountName | BillingStreet | AnnualRevenue | LastModifiedDate |
ABC | 123, Ginger Street | 50000.00 | 2010-03-15 00:00:00.000 |
Query:
SELECT AccountName,BillingStreet,AnnualRevenue,convert(varchar,cast(DBAccount.LastModifiedDate as datetime),126) as 'LastModifiedDate' FROM DBAccount.
Result:
ABC | 123, Ginger Street | 50000.00 | 2010-03-15T00:00:00 |
When integrating data from Salesforce to SQL Server, we can map the DateTime field from Salesforce to SQL Server Directly.