CODEWITHSUNDEEP
sql-serverssisnulletlderived-column
marko
marko

Reputation: 517

ssis derived column - check if date field is null

In SSIS derived column I need to check if a Date field is empty or null, I'm using this expression but I got an error:

TRIM([date field]) == "" ? NULL(DT_DATE) : TRIM([date field])

The field is of DT_DATE data type. What's wrong in that?

I've used expressions like this successfully:

TRIM(String_field) == "" ? NULL(DT_WSTR,255) : TRIM(String_field)

Upvotes: 4

Views: 7571

Answers (2)

Hadi
Hadi

Reputation: 37313

Concerning the error, it is related to the derived column result data type, since in conditional operators (?:) both true and false expression must generate the same data type.

In addition, your expression doesn't check for NULL values, you should use the following expression:

TRIM(REPLACENULL([date field],"")) == "" ? NULL(DT_DATE) : (DT_DATE)TRIM([date field])

Also, you have to make sure that [date field] can be parsed as date, so (DT_DATE)TRIM([date field]) will not throw an exception, you can use the error output to handles the value that doesn't contain a valid date.

Upvotes: 1

KeithL
KeithL

Reputation: 5594

You are combining data types (if true is a date and false is a string).

This is the correct formula:

TRIM([date field]) == "" ? NULL(DT_DATE) : (DT_DATE)TRIM([date field])

I am assuming [date field] is a string.

Upvotes: 3

Related Questions