loading data from Oracle

• Chander-314098

  SSC Enthusiast

  Points: 161

  More actions

  November 29, 2007 at 5:03 am

  #181587

  Hi,

  I'm trying to automate the data loading process in sqlserver. The source database is Oracle(9.2) where EPOCH time is being used. The target database (host) is SQLServer(7). The data is loaded through DTS package which is run manually once a week. The 'data to be loaded' is filtered in the where clause in the DTS package where the date range is manually changed before each run.

  Script in the DTS Package(in SQLServer) to extract the data from Oracle:

  [font="Arial"]SELECT "Username", "Company Name", "Assigned to", "Case ID", "Case Type", "Create Date", "Status", "Submitter", "Product Group", "Product Type", "DSL Phone number" , "Priority","ASAM Port"

  FROM "CO:Helpdesk" CO_Helpdesk

  WHERE ("Create Date" > {ts '2007-11-20 22:15:22.000'})[/font]

  My Objective is to automate this process by fixing the option manually changing the date range. To do this I'm not able to fetch the "EPOCH" version of "SYSDATE" in oracle.

  I tried number of option like below but its failing at the parse stage:

  WHERE ("Create Date" = CURRENT_TIMESTAMP)

  Please help me out on this.

  (Its the remedy database in Oracle)

  regards

  Chander

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 29, 2007 at 5:32 am

  #755351

  i googled for "oracle epoch date" and found a lot of stuff.

  one of the more obvious selectiosn was where they took the datatime as a double, and subtracted it from the desired date:

  http://www.freelists.org/archives/oracle-l/03-2004/msg00120.html

  select to_date('02-13-2004 17:14:06','MM-DD-YYYY HH24:MI:SS') - 38030.7181 from

  dual;

  i think the epoch date starts at year zero, or 0000-01-01, and not 1900-01-01, right?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• sreejithsql

  SSCommitted

  Points: 1813

  More actions

  January 24, 2008 at 3:26 pm

  #771526

  hi lowel,

  i also feel like wht u said.

  thx

  sreejith

  MCAD

• steve smith-401573

  SSChampion

  Points: 10889

  More actions

  February 7, 2008 at 12:29 pm

  #776143

  At this page, http://geekzspot.blogspot.com/2007/02/oracle-earliest-possible-date.html, you find the following:

  The Oracle Epoch Date (that is to say, the earliest or oldest date when the calender begins) can be found using

  select to_date('1','J') from dual;

  Which means that, in Oracle, you can have Oracle tell you what the value of the epoch date is. It would be reasonable to assume that, just like Microsoft has changed the definition of '0' date over time, Oracle has, as well.

  Also, note that 'from dual' is the Oracle equivalent to a SELECT statement in SQL Server without a FROM clause. I believe that in Oracle there is a 'today' or 'now' built-in function that will allow you to do date arithmetic for controlling the comparison, which is what you want to avoid manually resetting the date each week, right? Too many languages, too little time to keep them all straight.

  What you may be looking for is a cutoff criterion for datetime, rather than Current_Timestamp, though. So I suspect you are using the wrong function.

  There is 'Current_Date' and 'Current_Timestamp'. For instance:

  In Oracle/PLSQL, the current_date function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.

  The syntax for the current_date function is:

  current_date

  Applies To:

  * Oracle 9i, Oracle 10g, Oracle 11g

  and

  In Oracle/PLSQL, the current_timestamp function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP WITH TIME ZONE value.

  A similar function to the current_timestamp function is the localtimestamp function. The difference between these two functions is that the current_timestamp function returns a TIMESTAMP WITH TIME ZONE value while the localtimestamp function returns a TIMESTAMP value.

  The syntax for the current_timestamp function is:

  current_timestamp

  Applies To:

  * Oracle 9i, Oracle 10g, Oracle 11g

  Hope this helps...