Attempt to convert String value to Date type unsuccessful

[scottr]

Hi ,
i am trying to load the date field from source into target column as it is.
source is flatfile.date format is MM/DD/YYYY, target is oracle9.2 column is defined as DATE.

the following error raising ,

while processing column "Call_Start_Date_Time" Value treated as NULL

Attempt to convert String value "02/09/05" to Date type unsuccessful

any idea what's wrong here..Thanks

[Sainath.Srinivasan]

OConv(IConv(YourDate, 'D/MDY[2,2,4]'), 'D-DMY[2,A3,4]')
for date

OR

OConv(IConv(YourDate, 'D/MDY[2,2,4]'), 'D-YMD[4,2,2]') : ' 00:00:00'
for timestamp

for 'mm/dd/yyyy' mentioned as format in your post

OR

OConv(IConv(YourDate, 'D/MDY[2,2,2]'), 'D-DMY[2,A3,4]')
for date

OR

OConv(IConv(YourDate, 'D/MDY[2,2,2]'), 'D-YMD[4,2,2]') : ' 00:00:00'
for timestamp

for 'mm/dd/yy' mentioned in form of data in your post

[ray.wurlod]

Find out what your default Oracle date picture is.

Code: Select all

SELECT date_column FROM table;

Then make sure that whatever you're passing to the OCI stage matches this format precisely using Iconv/Oconv functions or substring/concatenation techniques.

[scottr]

i entered some date into XSl sheet in the format 02/09/2005 and saved it as .CSV

then ftp'd the file to the server

and RAY the column can accept date as 02/09/2005 (i have one more column as date and it's accepting the derivation DATE() )

although it look like stright forward ,but some how throwing errors

LdConnectedXXXHistory..Transformer_2: At row 16, link "DSLink4", while processing column "Call_Start_Date_Time" Value treated as NULL
Attempt to convert String value "09/02/2005" to Date type unsuccessful

[ray.wurlod]

If it's accepting Date() then it appears that the stage is expecting a date in DataStage internal format.

Try using just the Iconv(), omitting the Oconv() from your derivation.

[Sainath.Srinivasan]

The error you are receiving is from DataStage and not from the RDBMS. DataStage date values are integer numbers.

[john@dtsisoftware.com]

[quote="ray.wurlod"]If it's accepting Date() then it appears that the stage is expecting a date in DataStage internal format.

Try using just the Iconv(), omitting the Oconv() from your derivation.[/quote]

Greetings, all.
Ray, your suggestion to drop the Oconv() solved my problem as well.
I thank you kindly.

My only question now is, does anyone know how to write the date format so that the leading zeros are retained?

When I use (IConv(Input.MY_DT,'D2/MDY[2,2,4]')), I always get the date back without the leading zeros...ie.q. 2/5/2005 instead of 02/05/2005.

Any ideas?

Thanks, all
john

[ketfos]

Hi,
As mentioned earlier

OConv(IConv(YourDate, 'D/MDY[2,2,2]'), 'D-MDY[2,A3,4]')

the above routine returns date as 02/09/2005 if YourDate = 02092005

Ketfos

[john@dtsisoftware.com]

[quote="ketfos"]Hi,
As mentioned earlier

OConv(IConv(YourDate, 'D/MDY[2,2,2]'), 'D-MDY[2,A3,4]')

the above routine returns date as 02/09/2005 if YourDate = 02092005

Ketfos[/quote]
Thanks for the response...fyi...I tried your idea before I sent my question, and I retried it....I just cut and pasted your response as you sent it, and it still doesn't give the leading zeros..

I also used OConv(IConv(Input.TRANS_DT, 'd/mdY[2,2,2]'), 'd-mdY[2,A3,4]')

and stilll no leading zeros. I read somewhere that for some op sys using an upper case "D" removed the leading zero for days less than 10 and lower case "d" retained the leading zeros...same for M and m for the months....but I haven't been able to prove it works for DataStage as to whether it is case sensitve or not.

Any other thoughts ?
thanks for the response, though...it was a good thought.

john

[Sainath.Srinivasan]

Small correction, it must be

OConv(IConv(YourDate, 'D/MDY[2,2,4]'), 'D-DMY[2,A3,4]')

(for a 4 digit year part as given in the example)

[ketfos]

Hi,

OConv(IConv(MydateInput, 'D/MDY[2,2,2]'), 'D-MDY[2,2,4]')

I have DS 7.5 client at Windows 2000

When I run the above routine with MydateInput 01022005 or 1/1/2001

It returns output of 01-02-2005 or 01-01-2001

Is it not what you want?

Ketfos

[Sainath.Srinivasan]

Upper case 'M' with A3 returns JAN, FEB etc whereas lowercase returns Jan, Feb etc.

If you are loading using OCI, you need the timestamp as YYYY-MM-DD HH24:MI:SS. If it is date, you need it in YYYY-MM-DD format.

[john@dtsisoftware.com]

[quote="Sainath.Srinivasan"]Small correction, it must be

OConv(IConv(YourDate, 'D/MDY[2,2,4]'), 'D-DMY[2,A3,4]')

(for a 4 digit year part as given in the example)[/quote]


How very odd !
javascript:emoticon(':?')
Confused

I edited the transform, saved, recompiled, ran this, (and this I cut and pasted from my transform)

OConv(IConv(Input.TRANS_DT, 'D/MDY[2,2,2]'), 'D-DMY[2,A3,4]')

and got the following error....
V2ARCDC_1..DATACONV: At row 1, link "Output", while processing column "TRANS_DT"
Value treated as NULL
Attempt to convert String value "05-Mar-2004" to Date type unsuccessful

No rows were copied to the database.

Yet, when I re-edited to
OConv(IConv(Input.TRANS_DT, 'd/mdY[2,2,2]'), 'd-mdY[2,A3,4]')
(also a cut and paste)

then saved, compiled and reran it, the results were that at least rows were copied to the database, but still with no leading zeros...

I am totally befuddled...I even tried this twice...same results....works in lower case, but not upper case., but still no leading zeros

thoughts ?

[ketfos]

Hi,
Here is ConvertDate routine.
If Arg1 = @null
then Ans = @null
Else
Ans = Oconv(Iconv(Arg1, "d/mdy" ), "d-dmy[2,a3,4]")
End

----
I wrote this routine. I reads input from seq file and writes to Oracle table. The target column in Oracle table is defined as Type Date.

In the transformer, I use ConvertDate(InTrans.Date_of_Injury)
In input seq file Date_of_Injury is defined as char and in Target is defined as varchar

Ketfos

[john@dtsisoftware.com]

Thanks, all. Decided on a work-around.
IConv(Input.TRANS_DT,'D/mdY[2,2,4]') for the transform, and then a stored procedure to write it in the proper format.

Much appreciation to all who responded.
Thanks
john