DSXchange

Posted: **Wed Aug 08, 2012 9:02 am**

I have an 8 digit integer formatted like YYYYMMDD, example would be 20120808 and I need in the transformer to convert it to a date so oracle will accept it.

I have tried using this code

Code: Select all

Oconv(Iconv(Arg1,"DYMD"),"D-YMD[4,2,2]")

but I just get

Attempt to convert String value "2007-11-08" to Date type unsuccessful

I've done some searching already and read people suggesting to use a StringToDate() function but when I try to use it, I get an invalid function error.

Posted: **Wed Aug 08, 2012 9:19 am**

Since you are in DataStage Server the error message is not from the conversion itself, but when that column is being written to your database, it is most likely using a different default format for dates. Once you convert your string to what the DB expects the job should work correctly.

Posted: **Wed Aug 08, 2012 9:31 am**

Do you know how to format to 08-AUG-2012?

Posted: **Wed Aug 08, 2012 10:45 am**

In my humble opinion, you do not want to match the "default date format". Rather, use the TO_DATE() function in your insert SQL, which will always work regardless of default format.

Code: Select all

TO_DATE(YourColumn,'YYYY-MM-DD')

Would work for the "2007-11-08" example posted earlier.

The OCI stage will actually generate that SQL if you let it and the data types are correct.

Posted: **Wed Aug 08, 2012 11:02 am**

You are right and I've been attempting to make it work that way.

On my source column meta it comes in as a Decimal( 8 ), my destination is a Date, in the SQL that datastage auto generates it is using TO_DATE()

Code: Select all

INSERT INTO ....
VALUES (...
MY_DATE_COLUMN=TO_DATE(:73, 'DD-MON-YYYY')
....
)

And in my transformer I process everything through this routine .

Code: Select all

If Arg1 > 0 And Iconv(Arg1,"DYMD":@VM:"MCN") Then Ans = Oconv(Iconv(Arg1,"DYMD":@VM:"MCN"), "D-DMY[2,A3,4]")

(If the data is zero I should treat as null. And I'm a newbie so I'm still working on how to properly test for empty or null values in the routine but I do know that on clean data it does format the numbers correctly)

and when I run the job this is the error I get

Code: Select all

Facility_Table_Load..Transformer_2: At row 1, link "DSLink4", while processing column "MY_DATE_COLUMN"
Value treated as NULL
Attempt to convert String value "08-NOV-2007" to Date type unsuccessful

So I'm just really scratching my head over this one as to how to properly transform a number 20120808 in a way that I can insert it into a date column.

Posted: **Wed Aug 08, 2012 3:17 pm**

Code: Select all

Fmt(InLink.TheDate,"####-##-##")

will yield 2012-08-08 format from 20120808.

Posted: **Wed Oct 24, 2012 2:18 pm**

I discovered that I was close in the my previous post. Since I'm doing the transformation in a transformer there isn't a need to use OCONV because at that point everything this still internal to datastage. Here's the finish routine I wrote to handle my situation:

Code: Select all

If Len(Arg1) = 8 And Iconv(Arg1,"DYMD":@VM:"MCN") 
Then
  Ans = Iconv(Arg1,"DYMD":@VM:"MCN")
  End
Else
  Ans = @null
  End

It checks to make sure that there are 8 digits and that date conversion is possible and that it's not just a random set of 8 digits that don't really make a date other wise it returns null.

DSXchange

Integer to Date

Integer to Date