-
I am having trouble retreiving the sysdate in my queries. If I run
SQLWKS> SELECT sysdate
2> FROM dual;
SYSDATE
--------------------
20-NOV-01
1 row selected.
I am trying to get the time stamp (HH:MM:SS) to show as well, as Oracle books show that it should, but am having no luck. Hopefully someone out there has a suggestion
-
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
-
select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;
-
Use the 'to_char' function to display the system date (or any date stored in a table):
select to_char(sysdate, 'MM/DD/YYYY HH:MI:SS') from dual;
Note that the minutes field is 'MI' (not to be confused with month which is 'MM'). Good luck.
-
Or, change your NLS_DATE_FORMAT variable:
Code:
SQL> select sysdate from dual;
SYSDATE
---------
20-NOV-01
SQL> alter session set nls_date_format="mm/dd/yyyy hh24:mi:ss";
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
11/20/2001 11:44:46
Jeff Hunter
-
that all depends on the nls_date_format set in the init.ora
else for that particular session the nls_date_format may be changed...
sonofsita
http://www.ordba.net
-
Originally posted by sonofsita
that all depends on the nls_date_format set in the init.ora
else for that particular session the nls_date_format may be changed...
Not really, the hierachies goes like this
NLS_LANG enviroment variable predominates all followed by
NLS_DATE_FORMAT at session level
NLS_DATE_FORMAT set in init.ora, instance level
NLS_DATE_FORMAT at database level
if none of above is not used then you have to check NLS_TERRITORY, if I remember right it was territory
-
the question is why the output is like that than the user wished. I totally agree with you as far as hierachies are concerned.
it is said "NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering."
That also can be set at the database, instance, session
So ultimately what is suggested..
at the database level it is at the time of creation..
that is alterable ?
(1) permanent fix--instance level -- init.ora
(2) temporary fix-- session level at sqlplus/svrmgr/sqlwks
sonofsita
http://www.ordba.net
-
I have only seen NLS_DATE_FORMAT set in init.ora under 2 circumstances
1. The application is three-tiered
2. Requirement of application
If you are using an ad-hoc tool I dont see a reason why you have to set this parameter at instance level :-?
Plus setting it in init.ora you are forcing everyone use that date format
-
"ad-hoc tool " - this is a good reason to leave it for each session..
"Plus setting it in init.ora you are forcing everyone use that date format" - this is also a good reason to desist from imposing nls_date_format..
"Requirement of application" is essential...in my experience the general users (OS level users authenticated to access database using the schema owner id and pw)are viewers and application uses the schema owner id and does all the ddl/dml and we have set standards... so it suits us. that also now defines the tiers.
Thanks Pando for a goos discussion.
The question gets reverted to stevenb who is to know his requirement and decide..
[Edited by sonofsita on 11-20-2001 at 05:36 PM]
sonofsita
http://www.ordba.net
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|