We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

on
Medium Priority
393 Views
Im trying to use logic for a derived column. This is what I have...
[Column 53] == "00/00/0000" ? "01/01/1801" : [Column 53]
Its obviously wrong but I dont think by much.
Logic:
If [Column 53] = "00/00/0000" Then
"01/01/1801"
ELSE
[Column 53]
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
The expression looks correct if your Derived Column is a DT_WSTR.

Commented:
Well I tried that but why couldnt I keep it as a date. I would be setting it to "01/01/1801" if it = "00/00/0000" else it would be the vaild date??

Commented:
it didnt work for DT_WSTR either though...
CERTIFIED EXPERT

Commented:
Please post a sample set of the date values vound in the file.

Commented:
They are either a valid date 02/04/2008 or invalid date 00/00/0000. Is a derived column the best thing to use? What would your syntax be in the expression for this?
CERTIFIED EXPERT
Commented:
Would a NULL date column be a good answer. If the file has zeros, return a NULL? OR do you want the date to be 1801?

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
I actually just made the row a string and made it a blank string if it had all zeros...It was the datatype that was messing me up I guess.... Thats waht you get for letting SSIS Suggest your data types for yuo. Thanks for your help and I will give you the points anyway...

Commented:
thanks
CERTIFIED EXPERT

Commented:
Cool
Here is the Derived Column code to return a NULL date value or the actual date value.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)[Column 0]
First I get rid of all spaces, just in case. Then, if the value is zeros I set it to a NULL db_date value. If it is not zeros, I convert value to a db_date. In my example I tested, here are the values I get from a text file:
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
02/04/2008
01/23/2006
00/00/0000
Here is the output. First the Original column, then the Derived Column second delimited by a comma.
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,
02/04/2008,2008-02-04
01/23/2006,2006-01-23
00/00/0000,

As you can see, the zeros are now NULL. Good Luck. Hogg

Commented:
thanks man....I will need you in the near future so I will talk to you again...
CERTIFIED EXPERT

Commented:
Actually, don't forget to get rid of the spaces in the last part either. Screen shot attached.
TRIM([Column 0]) == "00/00/0000" ? NULL(DT_DBDATE) : (DT_DBDATE)TRIM([Column 0])

derived-with-null-date.bmp
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile