Date Manipulation

  • 899Views
  • Last Post 09 November 2017
  • Topic Is Solved
0
votes
Michael Kumaraswamy posted this 01 November 2017

Hi,

I am using Skyvia to move data from SQL Server to Salesforce. The first task is mapping about 15 fields in which 2 of them are date fields. The source data for these 2 fields are 12/31/9999 - Unfortunatley Saleforce allows a max year of 3999 . I tried using REPLACE(cms_expiration,9999,3999) and it gives a cast error 

The function "REPLACE" does not support the data type "DT_DBTIMESTAMP" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Can you please let me know how to make this translation ?

Thank You

date-fields expressions

Attached Files

Order By: Standard | Newest | Votes
1
votes
Mariia Zaharova posted this 02 November 2017

Please try using the following Expression:

YEAR(cms_expiration) > 3999 ? DATEADD("Year", 3999 - YEAR(cms_expiration), cms_expiration) : cms_expiration

 

Does this suit your needs?

  • Supported by
  • Michael Kumaraswamy
0
votes
Michael Kumaraswamy posted this 02 November 2017

Thank You Mariia ! It worked. The documentation seen in Skyvia site did not have this detail . Is there any other detailed help resource/documention available to help with mapping ? 

0
votes
Simon Bubnov posted this 06 November 2017
0
votes
Michael Kumaraswamy posted this 07 November 2017

Thanks Mariia - I have made some good progress with the mapping. I am stuck with one and I hope you can help me with it

Source data type is DT_I4 and Target data type DT_BOOL - I have tried different ways of typecasting the values but always end up with 

"The data types "DT_BOOL" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator."

Source values are eithet 0 (or) -1 . The transformation logic is if -1 then we should set the target to False and if 0 then True.

Thank You

0
votes
Mariia Zaharova posted this 08 November 2017

You can try using this expression:

SourceColumn == 0 ? True : False

 

Please tell us the result.

0
votes
Michael Kumaraswamy posted this 08 November 2017

Get the same error

majoracctdiscid == 0 ? True : False 

Source :majoracctdiscid which is DT_I4

Target :Statement_of_Work__c DT_BOOL

The data types "DT_BOOL" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

1
votes
Mariia Zaharova posted this 09 November 2017

The data types "DT_BOOL" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

This error may occur in case of incorrect condition in the left part of the expression. For example, majoracctdiscid == 0, may lead to this error in case if  majoracctdiscid column has bit datatype in the SQL Server database. However, if this column has int datatype, this expression works as expected.

Thus, please check the following:

1) check data type for this column in your database, click Refresh in the Task Editor to be sure that correct datatype is used in Skyvia:

2) if majoracctdiscid is int, in Skyvia it will be DT_I4 and you should use this expression:

majoracctdiscid == 0 ? True : False

 

3) if majoracctdiscid is bit, in Skyvia it will be DT_BOOL and you should use this expression:

majoracctdiscid == True ? True : False , or use direct Column mapping

 

If all this information doesn't help, please send us the DDL script for the used database table (so that we can check actual datatypes), set the corresponding Expression mapping (with which the error occurs) and save the package.

 

Looking forward to your reply.

  • Supported by
  • Michael Kumaraswamy
0
votes
Michael Kumaraswamy posted this 09 November 2017

Thank You Mariia - It worked

New Ticket
Categories
Popular Tags
Close