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

[dprasanth]

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.

Hi,
I thought I willl share this with you all. I had the input row from sequential file
3|DET|1|517032108|NULL|97|Apr 12 1998 12:00AM|E|M|SYSTEM|09-06-2002|N|END
where I had to convert Apr 12 1998 12:00AM to oracle compatible from. So I used the following transformation for the date column

Oconv(Iconv(DSLink17.AttendanceDate[1,11],"D MDY"),"D-YMD[4,2,2]") : " " :OCOnv(Iconv(DSLink17.AttendanceDate[13,8],"MTHS"),"MTS")

I first converted the date part using
Oconv(Iconv(DSLink17.AttendanceDate[1,11],"D MDY"),"D-YMD[4,2,2]") and then to convert the time string I used the OCOnv(Iconv(DSLink17.AttendanceDate[13,8],"MTHS"),"MTS" part.
I found it very useful as I could change AM and PM just to hh:mm:ss format which I wanted.

[chulett]

What do you mean by 'work with the sql being generated'?

You've done the right thing in my book - treat the Oracle DATE field as a Timestamp datatype and format what you received into what Oracle was expecting. I wouldn't go switching to user defined sql or anything. And handling the two pieces (date, time) separately *is* the way to go.

Standardize on this. Build up some common routines to handle dates and timestamps. Done right, you'll never have a problem handling Oracle dates again regardless of the NLS_DATE format of the target system.

[dprasanth]

What do you mean by 'work with the sql being generated'?

You've done the right thing in my book - treat the Oracle DATE field as a Timestamp datatype and format what you received into what Oracle was expecting. I wouldn't go switching to user defined sql or anything. And handling the two pieces (date, time) separately *is* the way to go.

Standardize on this. Build up some common routines to handle dates and timestamps. Done right, you'll never have a problem handling Oracle dates again regardless of the NLS_DATE format of the target system.

Thanks for that.
Sorry it was a typo when I wrote 'work with the sql being generated'?

[at095nb]

Pay attention. You are trying to load date formatted as DD MM YYYY HH:MM ( "Attendance_Date = 12 04 1998 12:00 ) with description of 'YYYY-MM-DD HH24:MI:SS'
Of course it's not working. You need to change one end (DS transformation) or another (Oracle SQL) whatever you are more comfortable with and which one is fit your requirement.