Page 1 of 1
Date Conversion
Posted: Thu Nov 15, 2007 1:55 am
by batswon
Hi,
I have a date conversion to do. The incoming source date format is in mm/dd/yyyy hh:mm:ss (i.e 1/1/2005 0:00:00) but I have to convert it into yyyy-mm-dd format. i.e (2005-01-01) How can I do it.
Please reply,
Thankyou
Posted: Thu Nov 15, 2007 2:51 am
by Maveric
StageVar : field(In.Col,' ',1) : "/"
field(StageVar,'/',3) : "-" : if Len(field(StageVar,'/',1)) = 1 then 0:field(StageVar,'/',1) else field(StageVar,'/',1) : "-" : if Len(field(StageVar,'/',2)) = 1 then 0:field(StageVar,'/',2) else field(StageVar,'/',2)
Yup. This should work. the first is stage variable derivation and the second is the output field derivation in the transformer.
Posted: Thu Nov 15, 2007 5:04 am
by ray.wurlod
What is the data type of the input column? It needs to be a string of some kind for Maveric's suggestion to work; if it's a date or timestamp you need to apply appropriate conversion functions. Given that you have one- or two-digit days and months adds complexity.
Posted: Fri Nov 16, 2007 9:16 am
by Raamc
Hi,
Here is the alternative way.
If your input is of type STRING then try out the following method.
SVmonth : If Len(DateVar[1,2])=1 Then 0:Trim(DateVar[1,2]) Else DateVar[1,2]
SVDay: If Len(DateVar[4,2])=1 Then 0:Trim(DateVar[4,2]) Else DateVar[1,2]
SVyear : DateVar(7,4]
FinalOP : SVyear:'-':SVmonth:'-':SVday
// SVmonth,SVDay,SVyear are staging Variables.