Databases
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