DSXchange

Hi,
I am very much new to datastage. I am having a sequential file that I am using as an input stage. The file has the following record format

1|517032108|"NULL"|97|"Apr 12 1998 12:00AM"|""|"M"|"SYSTEM"|"09-06-2002"|"N"|"END^M"

I have to convert the date Apr 12 1998 12:00AM to 12-Apr-1998 12:00AM. Can you please let me know how this can be done.

Thanks in Advance
Dprasanth

Hi,
Welcom Aboard :D
I havnt tried sever job codes yet, still this should work
regards
kumar

You need to do this in 2 steps, first convert the date portion into internal format telling it what the formatting is, then convert it to an output format in the new format.

OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99]

This takes your text date and tells Datastage the the format is D4MDY, so Date format, 4 digits for the year, in the order month, day, year. DataStage is intelligent enough to understand that the separator is space and that the month is in text form. The output just shifts the ordering around.

ArndW wrote:You need to do this in 2 steps, first convert the date portion into internal format telling it what the formatting is, then convert it to an output format in the new format.

OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99]

This takes your text date and tells Datastage the the format is D4MDY, so Date format, 4 digits for the year, in the order month, day, year. DataStage is intelligent enough to understand that the separator is space and that the month is in text form. The output just shifts the ordering around.

Anrdw.. I tried what you told me and still I am not able to find the date formated. The output file shows me the same result.
Kumar,
I tried your conversion also, but it doesn't work

ArndW wrote:You need to do this in 2 steps, first convert the date portion into internal format telling it what the formatting is, then convert it to an output format in the new format.

OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99]

This takes your text date and tells Datastage the the format is D4MDY, so Date format, 4 digits for the year, in the order month, day, year. DataStage is intelligent enough to understand that the separator is space and that the month is in text form. The output just shifts the ordering around.

I have a small query, sorry if I sound silly, I don't understand why we have D4 OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99].Does that signify anything?

dprasanth wrote:

ArndW wrote:You need to do this in 2 steps, first convert the date portion into internal format telling it what the formatting is, then convert it to an output format in the new format.

OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99]

This takes your text date and tells Datastage the the format is D4MDY, so Date format, 4 digits for the year, in the order month, day, year. DataStage is intelligent enough to understand that the separator is space and that the month is in text form. The output just shifts the ordering around.

I have a small query, sorry if I sound silly, I don't understand why we have D4 OCONV(ICONV(In.DateColumn[1,11],'D4MDY'),'D4DMY'[2,3,4]):In.DateColumn[12,99].Does that signify anything?

Andw,
Sorry.. i think there is some problem with our input file. i will just reformat the input file and will try your conversion again

dprasanth,

yes, it does signify something, it is the output format. Please open up the BASIC programmer's guide on your PC and look up the chapter on conversion codes, specifically for the Date conversion. There are examples in there that should clear up some of your questions.

ArndW wrote:dprasanth,

yes, it does signify something, it is the output format. Please open up the BASIC programmer's guide on your PC and look up the chapter on conversion codes, specifically for the Date conversion. There are examples in there that should clear up some of your questions.

Excellent.. thanks for your advice.
Thanks
Dprasanth

There are also good examples in on-line help for Oconv.

This article is also informative.

ray.wurlod wrote:There are also good examples in on-line help for Oconv.

This article is also informative.

Thanks a lot to everyone who answered my query. I was able to get loads of information by going through the documents

I tried using the conversion Oconv(Iconv(DSLink17.AttendanceDate[1,11],"D MDY"),"D DMY[2,2,4]"):DSLink17.AttendanceDate[12,99] and I am able to convert teh date from the input format to the output format. But Oracle is complaining when I am inserting that to the database
This is the input record
3|DET|1|517032108|NULL|97|Apr 12 1998 12:00|E|M|SYSTEM||| and this is the output record

RNK_SEQOCI_V2..Transformer_29: The value of the row is: ATTENDANCE_ID = 1 CUSTOMER_ID = 517032108 HostMember_ID = 0 Club_ID = 97 Attendance_Date = 12 04 1998 12:00
Oracle gives me this error
RNK_SEQOCI_V2..Transformer_29: ORA-01861: literal does not match format string.

Can any one of you please let me know what has to be done about this/

Doesn't Oracle expect a '/' instead of a space in the default date format?

ArndW wrote:Doesn't Oracle expect a '/' instead of a space in the default date format?

I did try / and -. But both are throwing me the same error

ArndW wrote:Doesn't Oracle expect a '/' instead of a space in the default date format?

This is the sql that is getting generated

INSERT INTO RNKT0.ATTENDANCE (ATTENDANCE_ID,CUSTOMER_ID,HostMember_ID,Club_ID,Attendance_Date) VALUES (:1,:2,:3,:4,TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'))
Can I change this sql somehow?what is the way of changing this generated sql. I what to change theTO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS')). Any help is greatly appreciated

dprasanth wrote:

ArndW wrote:Doesn't Oracle expect a '/' instead of a space in the default date format?

This is the sql that is getting generated

INSERT INTO RNKT0.ATTENDANCE (ATTENDANCE_ID,CUSTOMER_ID,HostMember_ID,Club_ID,Attendance_Date) VALUES (:1,:2,:3,:4,TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'))
Can I change this sql somehow?what is the way of changing this generated sql. I what to change theTO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS')). Any help is greatly appreciated

I got the above working by just changing the oconv. But if someone can tell me how to work with sql being generated that willl be great.