Home » SQL & PL/SQL » SQL & PL/SQL » SYSDATE (Oracle 9i)
SYSDATE [message #392005] Sun, 15 March 2009 23:33 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
When I run the sysdate in Oracle SQL*Plus, I am getting the only date.

select sysdate from dual;

SYSDATE
---------
15-MAR-09


When I run the sysdate in SQL Navigator 4.2, I am getting the date and time.

select sysdate from dual;

SYSDATE
---------
3/15/2009 11:17:18 PM


Please explain why the different output is showing in both the application because i read in the book "SYSDATE is a date function that returns the current database server date and time".
Re: SYSDATE [message #392007 is a reply to message #392005] Sun, 15 March 2009 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SYSDATE is a DATE datatype.
What get displayed is a "string"
What control default conversions?
Re: SYSDATE [message #392009 is a reply to message #392007] Sun, 15 March 2009 23:46 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
This is nothing, It is related to SESSION level setting for your DATE.

SQL> COLUMN DT FORMAT A20
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'
Session altered.
SQL> SELECT SYSDATE AS DT FROM DUAL

DT                  
--------------------
16-03-2009          

1 row selected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'
Session altered.
SQL> SELECT SYSDATE AS DT FROM DUAL

DT                  
--------------------
16-03-2009 04:44:50 

1 row selected.


Thanks
Trivendra
Re: SYSDATE [message #392010 is a reply to message #392005] Sun, 15 March 2009 23:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There is nothing to worry about.

You are firing the queies from two interfaces to Oracle Databases. Their session setting for Date would be different. That is why their output varies. What varies is just display.

But internally both selecting the the same value .

Smile
Raj.
Re: SYSDATE [message #392011 is a reply to message #392009] Mon, 16 March 2009 00:29 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks,yes it is working now. But I have doubt since SESSION level setting changed, persistance of the setting will stay only to that particular session. How do i change the default setting that is 'DD-MON-YY' to 'DD-MON-YYYY HH:MI:SS' in Oracle SQL*Plus that is whenever I run the query select sysdate from dual i should get 'DD-MON-YYYY HH:MI:SS'?
Re: SYSDATE [message #392016 is a reply to message #392011] Mon, 16 March 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use implicit format, always use TO_CHAR function if you want a specific one.

Regards
Michel
Re: SYSDATE [message #392020 is a reply to message #392005] Mon, 16 March 2009 01:11 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

In that case you can try for glogin.sql or login.sql for the ALTER SESSION statement for NLS_DATE_FORMAT. It will change the session for all Sql*Plus connection for the Date format. (not Just for SYSDATE).
Previous Topic: Outer Joins Concept
Next Topic: error when excuting procedure
Goto Forum:
  


Current Time: Thu Dec 08 08:17:18 CST 2016

Total time taken to generate the page: 0.11292 seconds