Subscribe to this thread
Home - General / All posts - M9 automatic conversion between data types in Edit > Schema > Add new field, but not in Transform
616 post(s)
#17-Oct-20 20:27

In the thread we learn that Cast is automatically to adjust for different data types where new fields are filled with data generated by expressions using data of different data types in other fields. However in the Transform pane, it seems this automatic casting isn't done. For instance in Edit > Schema I have one filed that is a text field with date, say 20120131 and I only want the year in a new Int16 field, so StringSubstringLen( [date_field], 0, 4) extracts the first 4 characters of text, converts to an integer and inserts into the new field of type Int16. However when I do the same operation in the Transform pane using Expression, I need to add the Cast function to do the conversion to integer from text. Otherwise in the Transform I get "nvarchar: Expression type mismatch" error.

It would be handy to have this automatic casting, however maybe there is a reason why not in the Transform pane?


6,233 post(s)
#18-Oct-20 19:53

Guessing with automatic casting is fudging it. Using cast is precise. See the discussion in various transform reference topics that give examples of CAST, or in this topic.

Ultimately, what's better than automatic casting is providing very simple means to change the type of a field that happens to be the wrong type for what you want, like right clicking a column head and choosing a Change Data Type command. That will get done.

616 post(s)
#19-Oct-20 21:45

Using cast is precise

OK, wrapping an expression with CAST (<value> AS <type>) is easy enough.

like right clicking a column head and choosing a Change Data Type command. That will get done.

Nice. Also I recently discovered the feature that hovering over a field name in a Table will show the data type. That's handy to confirm the data type of the destination field when formulating an expression.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.