CODEWITHSUNDEEP
sqloracle-databaseoracle11gssistimestamp
sab
sab

Reputation: 348

Oracle column with Timestamp(6) data type extract using ssis

I am trying to pull an oracle table data into sql server using SSIS. I have a package variable that holds the source query that needs to be fired in the oracle db.I have a data flow task with oledb source(oracle) and oledb destination(sql server).

The oledb source query(variable) is as follows

Select A,B,C 
  From "Table" TT  
 Where C in (Select coalesce(ab,cd) as c 
               From "Table" T2 
              Where Last_Upd_Dt >= '2018-09-24 12:00:00')

The column Last_Upd_Dt is a TimeStamp(6) with default value of LocalTimeStamp in the source oracle DB

My question is in what format should my input parameter value be so that I dont have to convert the Last_Upd_Dt column to TO_DATE(), TO_CHAR() etc.

If I run that query using SSIS I get

ORA-01843: not a valid month

Upvotes: 1

Views: 2632

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Oracle supports the DATE and TIMESTAMP keywords. You can express the logic as:

where Last_Upd_Dt >= TIMESTAMP '2018-09-24 12:00:00'

If you did not have a time component, you would do:

where Last_Upd_Dt >= DATE '2018-09-24'

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65383

Just use 

... Where Last_Upd_Dt >= to_date('2018-09-24 12:00:00','yyyy-mm-dd hh24:mi:ss')

or

... Where Last_Upd_Dt >= timestamp'2018-09-24 12:00:00'

you may refer the following demonstration :

SQL> create table tab ( id int not null, time timestamp(6) default LocalTimeStamp not null );

Table created

SQL> insert into tab(id) values(1);

1 row inserted

SQL> select * from tab t;

ID TIME
-- --------------------------
 1 26/09/2018 08:23:23,068025


SQL> select * from tab where Last_Upd_Dt >= to_date('2018-09-24 12:00:00','yyyy-mm-dd hh24:mi:ss');

ID TIME
-- --------------------------
 1 26/09/2018 08:23:23,068025

SQL> select * from tab where Last_Upd_Dt >= timestamp'2018-09-24 12:00:00';

ID TIME
-- --------------------------
 1 26/09/2018 08:23:23,068025


SQL> select * from tab where Last_Upd_Dt >= to_date('2018-09-26 12:00:00','yyyy-mm-dd hh24:mi:ss');

ID TIME
-- --------------------------   
                              --> no rows selected

Upvotes: 1

Related Questions