Using Date Function in DBSync Integration

Using Date function in DBSync

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.

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

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'").

Salesforce to MySQL Integration

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'").

SQL Server to Salesforce Integration

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

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.