Date Conversion

[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

[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.

[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.

[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.