question

MickiArruda-2207 avatar image
0 Votes"
MickiArruda-2207 asked ZoeHui-MSFT commented

Derived Column, DT_STR to DT_DBTIME2 Conversion with Nulls syntax

This is my string that works if the data has values in it

(DT_DBTIME2,7)(SUBSTRING(TMLMOD1,1,2) + ":" + SUBSTRING(TMLMOD1,3,2) + ":" + SUBSTRING(TMLMOD1,5,2))

It fails when there are nulls in the field.

Can you tell me how to check for nulls?

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MickiArruda-2207 avatar image
0 Votes"
MickiArruda-2207 answered

I tried this and it does not work if the field is empty

(TMLMOD1 == "") ? NULL(DT_DBTIME2,7) : (DT_DBTIME2,7)(SUBSTRING(TMLMOD1,1,2) + ":" + SUBSTRING(TMLMOD1,3,2) + ":" + SUBSTRING(TMLMOD1,5,2))

But still works if I put data in that field.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @MickiArruda-2207,

I tried with the expression you provided, it works well in my side.

Could you please have a double check or share the error message with us?

91921-screenshot-2021-04-28-094635.jpg

Also you may try with below expression to see if it is helpful.

 (TRIM(TMLMOD1) == "") ? NULL(DT_DBTIME2,7) : (DT_DBTIME2,7)(SUBSTRING(TMLMOD1,1,2) + ":" + SUBSTRING(TMLMOD1,3,2) + ":" + SUBSTRING(TMLMOD1,5,2))

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

May I know how's the issue going on now?

0 Votes 0 ·