Unicode string[DT_WSTR] to numeric[DT_NUMERIC] results in 0

Hi, I have a Derived Column that transfers a 'number' in a unicode string[DT_WSTR] data type to a Data conversion task the input column value is .0786 and I am converting to a numeric[DT_NUMERIC] data type after the Data Conversion task I can see in my data viewer that the input value has remained at .0786; however, the output from the data conversion to a numeric data type has outputted as 0
I'm not sure how to troubleshoot this or what would be causing this? Any help is appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is the defined length of the nvarchar column? If it isn't specified that may be the problem.
or, when casting/converting to numeric what (width,dec-places) are you using?

when you judge that the result is 0 are you sure that hasn't just been rounded?

declare @DT_WSTR nvarchar(8) = N'.0786'

select @DT_WSTR , cast(@DT_WSTR as numeric(8,4)) , cast(@DT_WSTR as decimal(8,4))

| .0786 | 0.0786 | 0.0786 |

Open in new window

Scarlett72Author Commented:
Hi PortletPaul, the length is blank, and I can't modify that, the precision is 18 and the scale is 0.  How can I modify these values?  {have attached screenshot}
try changing scale to 4 (I don't use SSIS, so no guarantee)

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Paul is correct.  Scale of 0 = no numbers after the decimal.
Scarlett72Author Commented:
I was confused thinking I had to modify the 'length' column, and couldn't.  ~Thank you,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.