Solved

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

Posted on 2008-10-01
4
840 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
  • 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 73

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now