Problem Of Date Conversion in DRS while retrieving data

Andal · Post by **Andal** » Wed Apr 27, 2005 10:35 pm

Hi,
I have a requirement in which I used a userdefined SQL in a Source DRS where I put
select START_DT,END_DT, LATE_START_DT from tablename.The fields have timestamp as the datatype.
I am using 2 databases(SQL Server and Oracle).The data in SQL server is in the format '1900-01-01 00:00:00'.The data in Oracle is of the format '04-Nov-03'.
The Job runs fine in SQL Server.But when I put it in Oracle, it is giving an error
"Attempt to convert String value '04-Nov-03' to Timestamp unsuccessful.
The query from the director is fetching the data perfectly.So this seems to be the problem with the fetching of data by the Source DRS in Ascential

If I modify the user defined Query as

select TO_CHAR(START_DT, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(END_DT, 'YYYY-MM-DD HH24:MI:SS')
from tablename ,
it will work fine in Oracle.But then it will fail in SQL Server since to_char is not a function in SQL Server.
Can anyone please suggest some solution to fix this issue in Oracle rather than using a To_Char function?

Thanks,
Andal

chulett · Post by **chulett** » Wed Apr 27, 2005 10:47 pm

Why does this need to be 'user defined' sql? Let the stage generate it.

Andal · Post by **Andal** » Wed Apr 27, 2005 11:09 pm

No actually some outer join is there in the end.I did not add the same to mke the query look less complex.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 28, 2005 5:39 am

Maybe you can set your NLS_DATE_FORMAT in your 'before sql' tab in your input.

chulett · Post by **chulett** » Thu Apr 28, 2005 7:34 am

Makes it kind of hard to provide good advice when you leave out details like that, Andal.

Don't worry, people here can read 'complex' sql. My only point was using user defined sql in a DRS stage removes any benefit of the 'D'ynamic part of it, unless you can stick with extremely generic sql.

At worst case, you may need two jobs.

Good luck!

ps - what is SQL Server going to do when it sees the NLS_DATE_FORMAT statement? Nothing good, I would guess.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 28, 2005 7:36 am

The NLS_DATE_FORMAT was intended to solve the Oracle date problem.

chulett · Post by **chulett** » Thu Apr 28, 2005 7:37 am

I understand. But they intend to switch it back and forth between Oracle and SQL Server, so the solution needs to be applicable to both.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 28, 2005 7:40 am

Then they can make it a parameter !!??

kollurianu · Post by **kollurianu** » Thu Apr 28, 2005 8:53 am

Hi Sai

where do you set this NLS_DATE_FORMAT in datastage NLS or in oracle?

Thank you very much,

kollurianu · Post by **kollurianu** » Thu Apr 28, 2005 9:15 am

Hi Andal,

Can you please send ur Datastage Job Design I am just curious how u used DRS stage in what scenario?

Thank you Very much,

Andal · Post by **Andal** » Fri Apr 29, 2005 2:38 am

Hi,
I got a resolution on how to use it.We can write the query like select %DateTimeOut(START_DT) from tablename.This will work both in SQL Server as well as in Oracle.
Craig, I did not mean that you are back in anything.Its only to reduce unnecessary complexity that I made the query like that. :D

Thanks,
Andal

DSXchange

Problem Of Date Conversion in DRS while retrieving data

Problem Of Date Conversion in DRS while retrieving data

Re: Problem Of Date Conversion in DRS while retrieving data