Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

828 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