SQL command format for columns

[Ionut Grigorescu_2]

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 :-(

[Manuel G · Accepted solution]

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]

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.

[Leslie Chaim]

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>

[Ionut Grigorescu_2]

That was even better!