Conversion of date from (Mon dd yyyy hh:mmAM) to (dd-mon-yyy

dprasanth · Post by **dprasanth** » Tue Oct 11, 2005 5:43 am

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

kumar_s · Post by **kumar_s** » Tue Oct 11, 2005 6:56 am

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

Code: Select all

Oconv(Iconv("Date_Time.field","DMTH"),"D-DMYMTH")

regards
kumar

ArndW · Post by **ArndW** » Tue Oct 11, 2005 6:59 am

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.

dprasanth · Post by **dprasanth** » Tue Oct 11, 2005 7:13 am

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

dprasanth · Post by **dprasanth** » Tue Oct 11, 2005 7:16 am

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 · Post by **dprasanth** » Tue Oct 11, 2005 7:31 am

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

ArndW · Post by **ArndW** » Tue Oct 11, 2005 7:34 am

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.

dprasanth · Post by **dprasanth** » Tue Oct 11, 2005 7:39 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Oct 11, 2005 3:04 pm

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

This article is also informative.

dprasanth · Post by **dprasanth** » Wed Oct 12, 2005 3:06 am

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

dprasanth · Post by **dprasanth** » Wed Oct 12, 2005 4:03 am

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/

ArndW · Post by **ArndW** » Wed Oct 12, 2005 4:09 am

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

dprasanth · Post by **dprasanth** » Wed Oct 12, 2005 4:13 am

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

dprasanth · Post by **dprasanth** » Wed Oct 12, 2005 4:34 am

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 · Post by **dprasanth** » Wed Oct 12, 2005 4:37 am

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.