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

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 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:

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.