Oracle date formats

shilpa79 · Post by **shilpa79** » Mon Apr 09, 2007 12:42 pm

I am trying to insert dates from SQL server to Oracle DB.

--------------------------------------------------------------------------------
Column derivations :
------------------------
---------------------------

Source------------------------------------------->target

SQL SERVER --------------------------------> ORACLE
Cloumn name sql tye ------------- Cloumn name sql type
---------------------------------------------------------------------------------
startdate Timestamp(23,3)-------> st_dt Timestamp(38
enddate Timestamp-------> end_dt Timestamp
upt_dt Timestamp-------> lst_ut_dt Timestamp

-------------------------------------------------------------------------------

I have changed the sql types for the Orcale columns to varchar and used
Timestamp to string function to insert into the table.

TimestampToString(in_lkpBU.StartDate,"%mm-%dd-%yyyy %hh:%nn:%ss")

Still I am getting error while doing that :

"LOOKUP: Type match failure for field ST_DT; APT Schema has string, dbms has type 12."
"LOOKUP: Describe failed because of a missing column for ST_DT"
"main_program: Could not check all operators because of previous error(s)"
"main_program: Creation of a step finished with status = FAILED."

Let me know If I have done anything is not correct

ray.wurlod · Post by **ray.wurlod** » Mon Apr 09, 2007 3:03 pm

How about wrapping your description in Code tags (edit your original post) so that the column headings and columns are aligned, and we can see what you mean by your design?

shilpa79 · Post by **shilpa79** » Tue Apr 10, 2007 10:16 am

ray.wurlod wrote:How about wrapping your description in Code tags (edit your original post) so that the column headings and columns are aligned, and we can see what you mean by your design?

Can anyone help me out......................

DSguru2B · Post by **DSguru2B** » Tue Apr 10, 2007 10:21 am

Did you try pass it as it is? What is the incoming timestamp format?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 10, 2007 1:48 pm

No, we can NOT help you out, because your design is not clear.
Wrap it in code tags, as requested.

shilpa79 · Post by **shilpa79** » Wed Apr 11, 2007 10:56 am

My design is

Sqlserverdb----------->Transform--------------->OracleDB

I would like to change the above date columns to load in Orcale . I am working in Parallel not in server .

sample date for the source columns
2007-04-04 00:00:00.000

Thanks,

kjosyula · Post by **kjosyula** » Wed Apr 11, 2007 11:04 am

How do you want the output in Oracle?
If you wish to see the data in the output column same as in input the datatype in oracle being varchar, Use TimestampToString(st_dt Timestamp).

Let me know if this answers your question.

-- JK

shilpa79 wrote:My design is

Sqlserverdb----------->Transform--------------->OracleDB

I would like to change the above date columns to load in Orcale . I am working in Parallel not in server .

sample date for the source columns
2007-04-04 00:00:00.000

Thanks,

DSguru2B · Post by **DSguru2B** » Wed Apr 11, 2007 11:47 am

Craig has explained "The oracle timestamp phenomenon" so many times. Please search the archives.
Also, what happens if you pass it as it is. The format you are showing is what oracle expects. No formatting might be required.

shilpa79 · Post by **shilpa79** » Wed Apr 11, 2007 1:29 pm

kjosyula wrote:How do you want the output in Oracle?
If you wish to see the data in the output column same as in input the datatype in oracle being varchar, Use TimestampToString(st_dt Timestamp).

Let me know if this answers your question.

-- JK

I have changed the sql types for the Orcale columns to varchar and used
Timestamp to string function to insert into the table.

TimestampToString(in_lkpBU.StartDate,"%mm-%dd-%yyyy %hh:%nn:%ss")

Still I am getting error while doing that :

"LOOKUP: Type match failure for field ST_DT; APT Schema has string, dbms has type 12."
"LOOKUP: Describe failed because of a missing column for ST_DT"
"main_program: Could not check all operators because of previous error(s)"
"main_program: Creation of a step finished with status = FAILED."

Thats wht I mentioned in my first draft that I tried .......................
Anyway I will check .....................

Thanks,

DSXchange