Weird characters for date field in Heterogenous Services.

[Rahul P]

Hi,

We have a Heterogenous Service database link setup to connect to a sql server database from our Oracle instances.

Whenever I query a table with date column in there, it gives us junk data.

I tried messing with various settings including NLS but, to no avail. It is the same with both the sql window and command window.

I tried the same sql in native sql*Plus and using Toad, and it shows the dates fine.

I couldn't find the relevant help in the help PDF. Please help.

Thank you.

 

[Marco Kalter]

Can you go to Tools > Preferences > SQL Window and enable the "Date fields to_char" option?

 

[Rahul P]

Works perfect. Thank you.

 

[Rahul P]

I have a follow up question. When I setup the date to to_char setting, I am not able to get the time element in the date. How do I go about getting that information? The NLS setting does have time in the date format.

 

[Marco Kalter]

The NLS_DATE_FORMAT controls the date and time format. To verify, execute this statement:

select value from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT'