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