CODEWITHSUNDEEP
sql-serverdatetimessisnullable
jackfrost5234
jackfrost5234

Reputation: 229

SSIS 2014 Derived Column, Ternary Expression to set date to null

In my package, I'm reading from a flat file. The file can have a date specified in one of the fields, or it can be left as a string of 0's.

If it is 0, I want to insert it as a null into my database. However I am unsure how to work around Date not being nullable.

Currently I am checking the to see if the file has 0's for this date in my script component. If it is 0, I set it to a default date of 1/1/1900.

Then in a derived column, I am trying to replace my out_period_end_dt column if the year of my Date is 1900 to set it as a null value, or just leave it as is otherwise. My expression:

YEAR(out_period_end_dt) == 1900? out_period_end_dt = null : out_period_end_dt

It throws an error stating that my expression is invalid. My column out_period_end_dt is of the DT_DBDATE type. I tried looking for a nullable date data type, but I wasn't able to find one. Suggestions?

Upvotes: 0

Views: 301

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

YEAR(out_period_end_dt) == 1900? NULL(DT_DBDATE) : out_period_end_dt

Upvotes: 1

Related Questions