Solved

How to change default date format in Oracle Development Tools .NET 2008 query Output Window

Posted on 2008-10-01
4
860 Views
Last Modified: 2013-12-18
Normally I do this once per session to get my dates in queries to return me datetime:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS'
But NET overrides this, do you know where the option is for this in .NET dev environment?
I looked in Tools|Options but its not there.
0
Comment
Question by:claghorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:claghorn
ID: 22620224
Just to be clearer, I am in the query window of .NET Tools For Oracle Development. aka ODT
Basically Visual Studio 8 with a plug-in.
I know that Oracle stores the date with time but Microsoft through this tool filters it to just show the date only.
I want to see datetime in my query results. Is there any way to make this happen?
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22623222
Setting NLS_DATE_FORMAT in the registry does not do the trick?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 100 total points
ID: 22630794
the problem is most likely in how you are retrieving the data.

If you pull a date type from Oracle into a .NET date type,  the nls_date_format does NOT apply.  

If you pull a date type from Oracle into a .NET string type, the nls_date_foramt WILL apply.

Date's don't have formats, only strings do.

So, if you have a grid control for instance, and bind your dataset of dates to the grid ,your dates will be rendered into strings by .NET, not by Oracle.

If your dataset has strings you converted in Oracle then your dataset will display the strings, as is.

I suggest ALWAYS using TO_CHAR with an explicit date mask and never using nls_date_format to try to get your dates to look a certain way.
For one, it's a built in reminder that you are converting your date to a string, and that's where the formatting matters.
Two,  you might not have permission to do alter session, but you'll always have permission to do to_char
Three, any session parameter that can be changed once, can be changed again, so your code will always have a design-hole in it if you trust nls_date_format to
do your date-to-string formatting for you.





0
 

Author Closing Comment

by:claghorn
ID: 31502028
I hear what you say about using to char sdstuber but I am pulling alot of code out of the packages
and pasting into the query window and running it to see what it returns. I then have to:
1. change the code to see datetime
2. change the code again to put back into the packages as I found it.
This is alot of work when changing the session once should suffice as it does in Oracle.
But since I'm using Microsoft I must do this extra work. It should be an option in the tool and I'll be requesting this for their next release of this query product.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question