One kind of transformation

original data type is string ,format like MM/dd/YYYY,also there exists null or "" value

I need to convert them into datetime,format like YYYY--MM-dd

any idea about this?

thanks in advance

Using a derived column and some simple string manipulation you could cut the string by the /'s and build it the way you want.|||

however, converting to datestamp failed

|||SSIS is rather rigid in it's conversion from string to DT.
See http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/14/16073.aspx for examples on strings that will work.|||

yes, it's not difficult to convert from string to DT

but i still could not figure out how to determine if the current value is null or not

just like that,

[value]!=""?(DT_Date)(): ....

how to define the otherwise portion? or other better method?

thanks

|||Use the isnull() function.|||

yes, i used

(trim([value])!=""&&isnul[value]==false)?(DT_Date)[value]: ....

but, how to deal with null or "" to convert to DT?

thanks

|||

Are you asking us what datetime value you should pass out if the incoming value is null? How do we know - that depends on your business rules.

I guess I'm misunderstanding.

-jamie

|||

Sorry for my explains not clear

I want to know how to let it work if value is null

many thanks

|||

(TRIM([value]) != "" && ISNULL([value]) == FALSE ) ? (DT_DBDATE)(SUBSTRING([value],FINDSTRING([value],"/",2) + 1,4) + "-" + SUBSTRING([value],1,2) + "-" + SUBSTRING([value],FINDSTRING([value],"/",1) + 1,2)) : NULL(DT_DBDATE)

this can work now, thanks

sql