Date format in DB2UDB database and DataStage

[shiva.chidara]

Hi all,

I have a column with data type Date and length 10.It is showing as 06/02/2006 in the database but when I am looking at the view data in the db2 stage in the datastage designer it is showing me as "14033 (2006-06-02)".

Now I just want to move the only date 06/02/2006 to the target stage.
I have tried straight mapping and used ICONV and OCONV functions and all the possibilities what ever but I am not showing up with the date I wanted in the target besides having the same datatype and same length for the both fields in source and target.

Any help is appreciated.
Thanx

[meena]

Hi Shiva,
When you view the data from the stage you will see both the internal format plus the external date too. And date representation will be showed in the database.
Can you post your expressions.

[shiva.chidara]

[quote="meena"]Hi Shiva,
When you view the data from the stage you will see both the internal format plus the external date too. And date representation will be showed in the database.
Can you post your expressions.[/quote]

I have used the derivation given below as
If IsNull(GetData.LAST_FREE_DATE) Then @NULL Else Oconv(GetData.LAST_FREE_DATE, "D-YMD[4,2,2]")

It is showing warnig in the log file as
while processing column "LAST_FREE_DATE"
Value treated as NULL
Attempt to convert String value "2006-06-02" to Date type unsuccessful

[meena]

Hi
If your source is db2 database then you can try passing it to target databse(db2) directly. Because the editor shows both format dates in this stage.
Else if the source is different then we have to use a expression based on the datatype.
But here I think you are passing data from DB2 table only. So , I prefer you to send it directly instead of ICONV or Oconv.And apply Nullable logic :

Code: Select all

If IsNull(GetData.LAST_FREE_DATE) Then @NULL GetData.LAST_FREE_DATE

Else if you want work with the expression then try in this way too:

Code: Select all

If IsNull(GetData.LAST_FREE_DATE) Then @NULL Else Iconv(Oconv(GetData.LAST_FREE_DATE, "D-YMD[4,2,2]"),"D/MDY[2,2,4]")

Once again the stage shows both formats.But you can check it in main database mean by running sql in any sql editors.
Check this.I am not sure whether this will work for you or not but I did the same..

[DSguru2B]

IF you target database is UDB too then just pass it as it is because DB2 accepts date in internal format. You dont need to explicity do any conversion to it.

[thurmy34]

Hi,

Here is my code :

Code: Select all

intern = Iconv(Trim(valeur , " ", "B"), "DYMD[4,2,2]")

where valeur is a string of 8 in the YYYYMMDD format.

Hope this help.

[kaps]

If your source date is in the format of yyyy/mm/dd then don't define your target datatype as date in DB2 stage. Just define it as Char(10) though it's date. DB2 stage will take care of everything...you don't need to iconv or oconv...