Problem Of Date Conversion in DRS while retrieving data
Moderators: chulett, rschirm, roy
Problem Of Date Conversion in DRS while retrieving data
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
![Confused :?](./images/smilies/icon_confused.gif)
At worst case, you may need two jobs.
![Wink :wink:](./images/smilies/icon_wink.gif)
Good luck!
ps - what is SQL Server going to do when it sees the NLS_DATE_FORMAT statement? Nothing good, I would guess.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Re: Problem Of Date Conversion in DRS while retrieving data
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
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