Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL command format for columns

SOLVED
Go to solution
Ionut Grigorescu_2
Super Advisor

SQL command format for columns

Hello,

a simple question:
How can I format a column containing the date in Oracle format so as to display after a select query also the hour and the minute (not only the date as until now)?. I have in a table period_start_time and period_stop_time columns but the display (after select...) show me only the date :-(
If it weren't for STRESS I'd have no energy at all
4 REPLIES
Manuel G
Frequent Advisor
Solution

Re: SQL command format for columns

Hi:

There is a Oracle Initialization Parameter for fixing date display format.

nls_date_format = "YYYY/MM/DD HH24:MI:SS"

You can find it on init"sid".ora.

I??m not sure you can change it without shutdown/startup database, maybe others could help on this.

If you don??t want to change the parameter, you can also format directly on select:

"SELECT TO_CHAR(PERIOD_START_TIME,'YYYY/MM/DD HH24.MI.SS') FROM ..."



Ionut Grigorescu_2
Super Advisor

Re: SQL command format for columns

Sorry for delay in answer..
I have solved the problem using a so called mediation device, which is an ASCII interface between the Oracle and external processing devices in our network. But I have tried your solution and it works! It's always good to learn something new...:-))- case closed.
If it weren't for STRESS I'd have no energy at all
Leslie Chaim
Regular Advisor

Re: SQL command format for columns

Just to continue the learning experience...

You don't have to change the parameter; you can do it by session basis.

SQL> select sysdate from dual;

SYSDATE
---------
02-OCT-02

SQL> alter session set nls_date_format = "YYYY/MM/DD HH24:MI:SS";

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2002/10/02 10:35:46

SQL>
If life serves you lemons, make lemonade
Ionut Grigorescu_2
Super Advisor

Re: SQL command format for columns

That was even better!
If it weren't for STRESS I'd have no energy at all