regarding date transformtions

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 1:04 am

hi gurus,
i got a column in my source as HIREDATE which has values as 08-SEP-81
23-MAY -87.i want to have the values in this column as 81/SEP/08 ,87/MAY/23.
iam using date transformation functions such as ICONV AND OCONV.
Oconv( Iconv("DSLink3.HIREDATE","%DYMD",DDMY[,2]) DSLink3.HIREDATE .

hiredate is the input column.
iam getting error,iam not able to derive transformation ,iam getting error.
plz help me in defining the transformation,am i going in the right way.
plz help me.

cheers;
vijay

DSGuru79 · Post by **DSGuru79** » Sun Dec 17, 2006 1:19 am

Can u be more specific about the error ?

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 1:34 am

Hi,
actually the HIREDATE column dervation turned into red, that means my column derivation is incorrect.
plz help me in derivation.plz help me in synatax of that column Hiredate- to which i applied date transformation functions.
this is the way i have defined.
Oconv( Iconv("DSLink3.HIREDATE","%DYMD",DDMY[,2]) DSLink3.HIREDATE .plz help me in syntax.
source column i/p is 21-may-1981,30-june-1987
i want o/p column as 1981/may/21, 1987/june/30
plz help me in syntax

cheers;
vijay

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 2:22 am

Hi Gurus,
sorry the information which i gave is incomplete.
i got a i/p column in my source.
hiredate
21-may-1981
27-june-1987
20-march-1989
i want the o/p column hiredate to be displayed as
hiredate
1981/may/21
1987/june/27
1989/march/20
for this, i used ICONV ,OCONV datetransformations for i/p column hiredate.
the syntax which i used is
Oconv( Iconv("DSLink3.HIREDATE","%DYMD",DDMY[,2]) DSLink3.HIREDATE
plz help me with syntax, as soon as i applied this date transformations column derivation turns red.
plz help me with syntax.

cheers;
vijay

kumar_s · Post by **kumar_s** » Sun Dec 17, 2006 2:56 am

Try this

Code: Select all

Oconv(Iconv(DSLink3.HIREDATE,"D-DMY[,A,]"),"D/YMD[A,]")

ray.wurlod · Post by **ray.wurlod** » Sun Dec 17, 2006 3:47 am

Whenever an expression is red, right click on it and choose Validate Derivation - let the expression editor tell you what is syntactically invalid.

The original expression (as you posted it) lacks a final right parenthesis.

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 1:00 pm

Hi gurus,
thanks very much, i could valid my expression.
but what if the i/p is in this format
1982-07-22 00:00:00
1981-09-28 00:00:00
iam following same derivation which you people helped me
Oconv(Iconv(DSLink3.HIREDATE,"D-DMY[,A,]"),"D/YMD[A,]")
when i validate and run, it doesn't load the records in the target.iam getting warning.
then i change the data type from TIMESTAMP to VARCHAR and i have choosen data element as DATETAG.
now iam able to run but IAM NOT ABLE TO SEE DATA IN HIREDATE COLUMN.
when i go to director to see the log file iam not even getting warnings.
plz help me, why am not able to view the data in hire-date column.

cheers;
vijay

DSguru2B · Post by **DSguru2B** » Sun Dec 17, 2006 1:09 pm

The input is now different from what you originally started with and hence the ICONV/OCONV expression will change. Also, ICONV/OCONV will not work for timestamps, you have to substring the date part and then use ICONV/OCONV.

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 2:13 pm

Hi gurus,
even if i include the substring my expression gets invalid.plz help me in syntax , how to deal with this type of data.
1980-12-17 00:00:00.
plz help me in syntax how to use my 0conv and Iconv transformations.
and get o/p as 17/12/1980 00:00:00

cheers;
vijaykumar

ray.wurlod · Post by **ray.wurlod** » Sun Dec 17, 2006 2:32 pm

Code: Select all

Oconv(Iconv(Field(InLink.TheTimestamp, " ", 1, 1), "DYMD"), "D/DMY[4,2,2]") : " " : Field(InLink.TheTimestamp, " ", 2, 1)

Don't use DATE.TAG data element. A Timestamp is not a DATE.TAG. Use Default or Timestamp if you must use a data element.

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 3:10 pm

hi,

when i use this synatx and valid my expression,
Oconv(Iconv(Field(InLink.TheTimestamp, " ", 1, 1), "DYMD"), "D/DMY[4,2,2]") : " " : Field(InLink.TheTimestamp, " ", 2, 1)

iam getting error
variable"inlink.timestamp" not defined.
when i add
Oconv(Iconv(Field(InLink.TheTimestamp, " ", 1, 1), "DYMD"), "D/DMY[4,2,2]") : " " : Field(InLink.TheTimestamp, " ", 2, 1) DSLINK3.HIREDATE
iam getting general syntax error.
plz help me in validating expression.

cheers;
vijay

vijaykumar · Post by **vijaykumar** » Sun Dec 17, 2006 4:34 pm

HI,
thanks for helping me but iam getting syntax error
Oconv(Iconc((Field(DSLINK3.HIREDATE, " ", 1, 1)"D-YMD[4,2,2]"),"D/DMY[2,2,4]") :" ": (Field(DSLINK3.HIREDATE, " ", 2, 1))

DSLINK3 is the i/p link.
Hiredate is the i/p column.
1980-12-17 00:00:00 is the format of the i/p data.
i want to transform to 17/12/1980.

iam getting syntax error.plz help me.

cheers;
vijay

kumar_s · Post by **kumar_s** » Sun Dec 17, 2006 5:07 pm

Hi Vijay,

The best approach is open the pdf to find the syntax and format of each functions that you use. So that you get to know about each functions that you start to use and you will start to modify the given example to your needs.
There should be two argument that need to be passed to Iconv function. First is input and other one is the format of input.
In your given example Iconc((Field(DSLINK3.HIREDATE, " ", 1, 1)"D-YMD[4,2,2]")
Field(DSLINK3.HIREDATE, " ", 1, 1) is the input and "D-YMD[4,2,2]" is the format of the input. There should be a ',' comma, seperating that.

ray.wurlod · Post by **ray.wurlod** » Sun Dec 17, 2006 6:11 pm

Not using metasyntactic variables (placeholders in syntax representations) instead of genuine operands in expressions will help too.

chulett · Post by **chulett** » Sun Dec 17, 2006 8:08 pm

Oooo... metasyntactic variables.