# Using Date Function in DBSync Integration

## **Using Date function in DBSync** <a href="#usingdatefunctionindbsyncintegration-usingdatefunctionindbsync" id="usingdatefunctionindbsyncintegration-usingdatefunctionindbsync"></a>

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

{% code overflow="wrap" %}

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

{% endcode %}

**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'").&#x20;

### **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'").&#x20;

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

{% code overflow="wrap" %}

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

{% endcode %}

**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.