We would like to do a SQL query on a master data extension from which there is a data column which was set as "Text" instead of "Number". We would like to make a conversion into the target DE from string to integer. However, we get a "query failed during execution – Conversion failed when converting the nvarchar value to data type int" error. Here's the portion of the SQL in Automation Studio referring to the columnSELECT CONVERT(INT,Address_ID) FORM table_1
. We also tried with CAST, namely: SELECT CAST(Address_ID AS INT) AS Address_ID FROM table_1
" but without any results. An example of a record is: 389085. Please help.
Best Answer
it worked for me this way:
SELECT CONVERT(FLOAT,A.Field) AS IntField FROM A
having IntField field type of target data extension = Number. You had better to insert also a control of null or empty values and a trim to delete blank spaces before conversion: SELECT...
FROM...