Conversion of date from (Mon dd yyyy hh:mmAM) to (dd-mon-yyy
Moderators: chulett, rschirm, roy
Conversion of date from (Mon dd yyyy hh:mmAM) to (dd-mon-yyy
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
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
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")
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.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.
Kumar,
I tried your conversion also, but it doesn't work
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?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.
Andw,dprasanth wrote: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?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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Excellent.. thanks for your advice.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.
Thanks
Dprasanth
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks a lot to everyone who answered my query. I was able to get loads of information by going through the documentsray.wurlod wrote:There are also good examples in on-line help for Oconv.
This article is also informative.
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/
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
This is the sql that is getting generatedArndW wrote:Doesn't Oracle expect a '/' instead of a space in the default date format?
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.dprasanth wrote:This is the sql that is getting generatedArndW wrote:Doesn't Oracle expect a '/' instead of a space in the default date format?
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