Need DS transformation function for this SQL command.

suneyes · Post by **suneyes** » Fri Aug 08, 2008 4:39 am

Hi All,
I have a requirement to implement the query

select
trim( to_number(to_date(&1,'yyyymmdd') - to_date(&2,'yyyymmdd')) )

in a column of a datastage transformer stage.

&1 and &2 are two columns in the transformenr input which are dates in "YYYYMMDD" format.

the output should column dhould contain the result of the query.

can anyone help me with some datastage functions which can be used to replace this query.??

ArndW · Post by **ArndW** » Fri Aug 08, 2008 4:48 am

DataStage server internal dates are Julian; so an

Code: Select all

ICONV(In.Date1,'D4YMD[4,2,2]')-ICONV(In.Date2,'D4YMD[4,2,2]'

should give you the numeric difference in days.

suneyes · Post by **suneyes** » Fri Aug 08, 2008 5:07 am

Thank you very much Arndw.
Iconv worked for my requirement.
thank you very much.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 08, 2008 5:31 am

Perhaps you can mark this thread as Resolved, then.