Timestamp convertion needed for Oracl Insert

[mandyli]

Hi

I am going to insert Create timestamp & update timestamp for Oracle Table. In the Oracle date type is Date for 2 both the columns
while I am using OCONV I am getting below error.

" while processing column "CRTE_TS"
Value treated as NULL
Attempt to convert String value "2008-7-04" to Date type unsuccessful "

Can any one help

[sachin1]

i think that your input date value is not in proper format,
first you convert your input date value in internal format using ICONV and then use OCONV with below format in which oracle OCI accepts date value for date field

Oconv(@date ,"D D-M-Y[,A3,2]")

[mandyli]

Hi

Thanks for your mail.

I am using following format for Create Timestamp and update timestamp

OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS:")


Still I am getting following error.

ORA-01830: date format picture ends before converting entire input string

[sachin1]

in your OCI stage for date field(column) change SQL type to Timestamp and it will work

[mandyli]

Thanks

Now I am using following way.

SUBSTRINGS(DSJobStartTimestamp,1,10):" ":SUBSTRINGS(DSJobStartTimestamp,12,8)

[ray.wurlod]

Substrings() is slightly less efficient, because it carries within it the ability to deal with multi-valued data. Prefer Left() and Right() functions or the square bracket notation for substringing.

[mandyli]

Thanks Ray,

In our ORACLE database it is Date and Datastage we are giving Timestamp datatype. still i am not able to load into Oracle table.

But If I am writing into Sequential File using DSJobStartTimestamp function it will write correct date and time format.


Pls help me for this one.

Thanks
Man

[keshav0307]

if you can use Upsert then you can write user defined query.
and can use TO_DATE function in insert query

[chulett]

What do you believe the 'correct date and time format' is? Apparently not what Oracle believes it to be.

If you are using generated SQL (as you should be IMHO) and a Timestamp datatype in the job (again, as you should be IMHO) then that format needs to be this:

YYYY-MM-DD HH24:MI:SS

If instead you are using user-defined sql and banking on knowing the NLS default date format of the target database then all bets are off. To me, and this is something I've repeated here ad nauseum, you need to be consistent and always build a full timestamp for an Oracle DATE field and use generated SQL to handle them. Your jobs will then have zero issues with date values and work with any Oracle instance regardless of the NLS_DATE setting.

And yes, that does mean specifically adding " 00:00:00" to a date when you are storing it "without a time".