Home » Other » Client Tools » How to convert dataTime format?
How to convert dataTime format? [message #25923] Tue, 27 May 2003 00:27 Go to next message
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!
Re: How to convert dataTime format? [message #25925 is a reply to message #25923] Tue, 27 May 2003 01:22 Go to previous messageGo to next message
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
Re: How to convert dataTime format? [message #25939 is a reply to message #25923] Tue, 27 May 2003 19:07 Go to previous message
AnotherBlue
Messages: 28
Registered: July 2001
Junior Member
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
Previous Topic: Having trouble with this complex query
Next Topic: Most Most Most urgent help need about temporary table and matrix reps
Goto Forum:
  


Current Time: Thu Mar 28 10:46:33 CDT 2024