Re: timestamp and date format

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Mon, 11 Dec 2006 13:58:28 GMT
Message-Id: <pan.2006.12.11.13.58.25.746623@verizon.net>

On Sat, 09 Dec 2006 07:59:01 +0100, Bruintje Beer wrote:

> Hi,
>
> I am new to oracle. How can I change the timestamp and date format to
> dd-mm-yyyy hh:mm:ss for timestamp and dd-mm-yyyy for date.
>
> Thanks
>
> John

You can define NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT in the environment. It works like this:

$ NLS_DATE_FORMAT="MM/DD/YYYY HH24:MI:SS" $ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 11 08:52:28 2006

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE

---

12/11/2006 08:52:37

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options $ NLS_DATE_FORMAT="DD-MON-YYYY"
$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 11 08:53:09 2006

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE

---

11-DEC-2006 SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options $

In my environment, I have set the "-a" flag which automatically exports all my variables. If you don't have that set, you should export the variables in question. On Windows environments, you should set it with your regedit program. These variables can also be set at the session level, by using "alter session set NLS_DATE_FORMAT='<format>';".

-- 
http://www.mladen-gogala.com

Received on Mon Dec 11 2006 - 07:58:28 CST