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
Date format in DB2UDB database and DataStage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Sat Jan 28, 2006 7:53 pm
-
- Participant
- Posts: 8
- Joined: Sat Jan 28, 2006 7:53 pm
[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
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
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 :
Else if you want work with the expression then try in this way too:
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..
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
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]")
Check this.I am not sure whether this will work for you or not but I did the same..
Hi,
Here is my code :
where valeur is a string of 8 in the YYYYMMDD format.
Hope this help.
Here is my code :
Code: Select all
intern = Iconv(Trim(valeur , " ", "B"), "DYMD[4,2,2]")
Hope this help.