Page History

Using Date function in DBSync

Date has always been one of the complex functions to be used in integration. DBSync has inbuilt date function that might be used in the mappings to convert date from source to target format.
Along with the inbuilt date function you can also utilize the date functions of various databases to convert the date at query level.

MySQL to Salesforce Integration

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

Query:
SELECT AccountName, BillingStreet, AnnualRevenue, DATE_FORMAT(LastModifiedDate,'%Y-%m-%dT%H:%i:%sZ') AS 'LastModifiedDate' FROM DBAccount

Result:

Using DBSync DATE() Function
We can also use 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'")

Salesforce to MySQL Integration

Using DBSync DATE() Function
We can also use 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 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'")

SQL Server to Salesforce Integration

Using SQL Server CONVERT() & CAST() functions
When we are reading from SQL Server database and 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

Query:
SELECT AccountName,BillingStreet,AnnualRevenue,convert(varchar,cast(DBAccount.LastModifiedDate as datetime),126) as 'LastModifiedDate' FROM DBAccount

Result:

Salesforce to SQL Server Integration

When integrating data from Salesforce to SQL Server we can map the DateTime field from Salesforce to SQL Server Directly.