OraFAQ Forum: Client Tools » How to convert dataTime format?

Home » Other » Client Tools » How to convert dataTime format?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to convert dataTime format? [message #25923]

Tue, 27 May 2003 00:27

caren
Messages: 7
Registered: September 2002

Junior Member

How to convert datetime format which can let user view date in hh:mm:ss:mmmAM (or PM) format ?
Example :

Table Name: TimeTable
==================
startTime
---------------------------
1900-01-01 13:00:00.000
1900-01-01 14:00:00.000
1900-01-01 15:00:00.000

But i want to view it in this format :
startTime
---------------------------
1900-01-01 01:00:00 PM
1900-01-01 02:00:00 PM
1900-01-01 03:00:00 PM

Thank you!

Report message to a moderator

Re: How to convert dataTime format? [message #25925 is a reply to message #25923]

Tue, 27 May 2003 01:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Dates are stored in an internal format. There are several ways that can tell how a date is being displayed. Look at the following:

SQL> SELECT sysdate
  2    FROM dual;

SYSDATE
---------
27-MAY-03

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYYY HH24:MI:SS'
SQL> /

Session altered.

SQL> SELECT sysdate
  2    FROM dual;

SYSDATE
-------------------
05-27-2003 08:40:08

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYYY HHPM:MI:SS';

Session altered.

SQL> SELECT sysdate
  2    FROM dual;

SYSDATE
---------------------
05-27-2003 08AM:40:26

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYYY HH PM MI:SS';

Session altered.

SQL> SELECT sysdate
  2    FROM dual;

SYSDATE
----------------------
05-27-2003 08 AM 40:42

SQL> conn mhe@test815 <B>NEW SESSION - date format back to default</B>
Enter password: ***********
Connected.
SQL> /

SYSDATE
---------
27-MAY-03

SQL>

This clearly shows that there is a default date format (called NLS_DATE_FORMAT) and in my case, the layout is 'DD-MON-YY'. I can change it by altering my session. It is stored in your registry too (for windows machines): HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_DATE_FORMAT.
If it's missing you're most probably using the database default. The database default is stored in the INIT.ORA (for pre-9i) or in the PFile.

Now for you, one suggestion might be to issue the alter session set nls_Date_format... whenever a user logs on to your application.

MHE

Report message to a moderator

Re: How to convert dataTime format? [message #25939 is a reply to message #25923]

Tue, 27 May 2003 19:07

AnotherBlue
Messages: 28
Registered: July 2001

Junior Member

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

Report message to a moderator

Previous Topic:	Having trouble with this complex query
Next Topic:	Most Most Most urgent help need about temporary table and matrix reps

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 31 03:10:44 CST 2025