topic SQL command format for columns in Operating System - HP-UX

SQL command format for columns

Ionut Grigorescu_2 — Tue, 01 Oct 2002 06:44:23 GMT

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

Re: SQL command format for columns

Manuel G — Tue, 01 Oct 2002 07:15:49 GMT

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 ..."

Re: SQL command format for columns

Ionut Grigorescu_2 — Tue, 01 Oct 2002 14:33:57 GMT

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.

Re: SQL command format for columns

Leslie Chaim — Wed, 02 Oct 2002 13:30:05 GMT

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>

Re: SQL command format for columns

Ionut Grigorescu_2 — Wed, 02 Oct 2002 14:42:22 GMT

That was even better!