November 10, 2009 at 2:59 pm
Hi,
I have incoming data in the format
08301995 which is a date in string format.
I need to convert it to datetime.
For that I am using the expression
SUBSTRING((DT_WSTR)DOB,5,4)+"-"+SUBSTRING((DT_WSTR,8)DOB,1,2)+"-"+SUBSTRING((DT_WSTR,8)DOB,3,2)+" 00:00:00.000"
which is working fine if there are no NULL values. But it is failing when it sees NULL values
I have added a function to handle NULL values and here is the expression
ISNULL(DOB) ? "NULL(DT_DBTIMESTAMP,50)" : SUBSTRING((DT_WSTR)DOB,5,4)+"-"+SUBSTRING((DT_WSTR,8)DOB,1,2)+"-"+SUBSTRING((DT_WSTR,8)DOB,3,2)+" 00:00:00.000"
which is supposed to put in a NULL value in the output column when it see a NULL value in the incoming column and if it doesnt it has to convert date to datetime.
Any ideas what else i have to include to get this working
Thanks,
November 11, 2009 at 7:45 am
If this is EXACT expression you are using:
ISNULL(DOB) ? "NULL(DT_DBTIMESTAMP,50)" : SUBSTRING((DT_WSTR)DOB,5,4)+"-"+SUBSTRING((DT_WSTR,8)DOB,1,2)+"-"+SUBSTRING((DT_WSTR,8)DOB,3,2)+" 00:00:00.000"
Then I think you need to replace:
"NULL(DT_DBTIMESTAMP,50)"
With:
NULL(DT_DBTIMESTAMP)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2009 at 8:07 am
That is not the reason it is failing. If it is the reason i wouldn't even able to validate the expression.
It is actually failing while running the package.
I have actually figured it out and the reason being blank values instead of NULL values.
So i have added in TRIM function to handle blank values.
Here is the expression i am using now and it is working fine.
(ISNULL(DOB) || TRIM(DOB) == "") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(DOB,5,4) + "-" + SUBSTRING(DOB,1,2) + "-" + SUBSTRING(DOB,3,2))
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply