date conversion

deva · Post by **deva** » Tue Feb 12, 2008 11:06 am

Hi ,
I am trying to load the data from file to database.

file have one date column begin_date its data type is timestamp(19), target database column type is timestamp (38). while loading the data I am getting the error, date for mante not supporting.

can any one suggetst on this.

paddu · Post by **paddu** » Tue Feb 12, 2008 11:09 am

Please post the input date format here .

kcbland · Post by **kcbland** » Tue Feb 12, 2008 11:31 am

What's the target database and can you paste the DML.

Teej · Post by **Teej** » Tue Feb 12, 2008 11:33 am

Please always paste the exact error message, not translate it or anything like that.

deva · Post by **deva** » Tue Feb 12, 2008 12:27 pm

Teej wrote:Please always paste the exact error message, not translate it or anything like that.

I am getting the follwoing error

Db_Insurance_Policy_Period..Risk_Tfm: DBMS.CODE=ORA-01861
Db_Insurance_Policy_Period..Risk_Tfm: ORA-01861: literal does not match format string

nivas · Post by **nivas** » Tue Feb 12, 2008 12:33 pm

use IConv and OConv datastage functions convert the input value appropriate to Oracle Date format. Look for Online document for IConv and OConv with different options.

deva · Post by **deva** » Tue Feb 12, 2008 12:39 pm

nivas wrote:use IConv and OConv datastage functions convert the input value appropriate to Oracle Date format. Look for Online document for IConv and OConv with different options.

I am using the following function

Oconv(Iconv(DSLink44.BEGIN_DATE , "D YM"), "D4/MDY")

it inserting nulls. its not null col. so all are rejecting

deva · Post by **deva** » Tue Feb 12, 2008 12:42 pm

deva wrote:
nivas wrote:use IConv and OConv datastage functions convert the input value appropriate to Oracle Date format. Look for Online document for IConv and OConv with different options.
I am using the following function

Oconv(Iconv(DSLink44.BEGIN_DATE , "D MDY"), "D4/MDY")

it inserting nulls. its not null col. so all are rejecting

nivas · Post by **nivas** » Tue Feb 12, 2008 12:44 pm

using IsNull datastage function handle the nulls set a default value

paddu · Post by **paddu** » Tue Feb 12, 2008 12:45 pm

Deva,

First of all you need to provide the input date format looks like .

It is not possible for anyone of us to provide right solution without this information.

ArndW · Post by **ArndW** » Tue Feb 12, 2008 12:46 pm

Iconv and Oconv have nothing to do with nullability. If your column contains nulls, add something like

Code: Select all

IF IsNull(DSLink44.BEGIN_DATE) THEN '01/01/2000' ELSE Oconv(Iconv(DSLink44.BEGIN_DATE , "D MDY"), "D4/MDY")

rsomiset · Post by **rsomiset** » Tue Feb 12, 2008 7:25 pm

Once again, you make sure that the incoming data is not null. If not, then use the iconv function with the format that your data is coming.

rsomiset · Post by **rsomiset** » Tue Feb 12, 2008 7:25 pm

Once again, you make sure that the incoming data is not null. If not, then use the iconv function with the format that your data is coming and oconv function in the format that you want.

deva · Post by **deva** » Wed Feb 13, 2008 9:05 am

rsomiset wrote:Once again, you make sure that the incoming data is not null. If not, then use the iconv function with the format that your data is coming and oconv function in the format that you want.

Hi All,
date column have value. But while converting null is comming out.

ArndW · Post by **ArndW** » Wed Feb 13, 2008 9:16 am

Are you talking about a NULL or an empty string? The former, in Server, can only occur if the input is already NULL!