Solved

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

Posted on 2008-10-01
4
861 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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…

734 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