DERIVED Column statement for date

Question

DERIVED Column statement for date

San 121

Hi Experts,

I want to generate a derived column if date is null then lastyear else thisyear

here is the statement
IF (DATE)==" ":YEAR(DATE_LY)):(YEAR(DATE)

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Monalv-MSFT 5,926

Hi @San ,

1.We can use the following expression in Derived Column:
Date == " " ? YEAR((DT_DATE)Date) - 1 : YEAR((DT_DATE)Date)

2.We should add error redirect after Derived Column.

Please refer to the following pictures:

Best Regards,
Mona

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 in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

San 121 Reputation points

2021-01-05T09:26:23.913+00:00

@Monalv-MSFT ,

getting an error.. here is my table
date date_ly
2017-12-27 00:00:00.000 2016-12-28 00:00:00.000 2017
2017-12-28 00:00:00.000 2016-12-29 00:00:00.000 2017
2017-12-29 00:00:00.000 2016-12-30 00:00:00.000 2017
2017-12-30 00:00:00.000 2016-12-31 00:00:00.000 2017
2017-12-31 00:00:00.000 NULL 2017
NULL 2017-01-01 00:00:00.000 NULL
2018-01-01 00:00:00.000 2017-01-02 00:00:00.000 2018
2018-01-02 00:00:00.000 2017-01-03 00:00:00.000 2018
Monalv-MSFT 5,926 Reputation points

2021-01-06T08:11:53.747+00:00

Hi @San ,

According to your example data, we can use the following expression in Derived Column.

date == "NULL" ? YEAR((DT_DBTIMESTAMP)date_ly) : YEAR((DT_DBTIMESTAMP)date)

Best Regards,
Mona
San 121 Reputation points

2021-01-06T09:10:29.197+00:00

@Monalv-MSFT ,

Thanks for the effort. getting this error
Monalv-MSFT 5,926 Reputation points

2021-01-06T09:57:45.2+00:00

Hi @San ,

Could you please share the data type of the columns [DATE] and [DATE_LY]?

Best Regards,
Mona
San 121 Reputation points

2021-01-06T10:15:50.667+00:00

@Monalv-MSFT

from src i am getting date as database date [DT_DBDATE]
Monalv-MSFT 5,926 Reputation points

2021-01-07T09:08:51.767+00:00
Hi @San ,

Please use ISNULL(expression) in Derived Column.
ISNULL(date) ? YEAR((DT_DBTIMESTAMP)date_ly) : YEAR((DT_DBTIMESTAMP)date)

We can open Advanced Editor for Source to check the data types of columns.

Please refer to the following pictures:

Best Regards,
Mona
San 121 Reputation points

2021-01-07T10:15:28.363+00:00

@Monalv-MSFT ,

Thanks its now working now

Share via

DERIVED Column statement for date

0 additional answers

Your answer