Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 73
VB.NET (2008) - Refactoring Question 2 19
ODBC in excel 2016 in Windows 10 via VBA 16 70
Help with consolidating excel files using VB.net 2 21
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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