| problem with date [message #127287] |
Mon, 11 July 2005 01:41  |
raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai
|
Junior Member |
|
|
hi everybody
create table t(d date);
insert into t values(to_date('09-05-2005 4:16:46 PM', 'dd-MM-yyyy HH:MI:SS AM'));
select * from t;
d
----------
09-may-05
the problem is
how should i get time using the select stmt.
and also is there is any way to keep track of the time values in a column
or is it possible to enter only time in Date type column;
|
|
|
|
| Re: problem with date [message #127289 is a reply to message #127287] |
Mon, 11 July 2005 01:46  |
somnath1974
Messages: 15
Registered: July 2005
|
Junior Member |
|
|
Hi,
Check the NLS_DATE_FORMAT value in the NLS_DATABASE_PARAMETERS table. I think is it is set to dd-mon-rr. You need to modify it to be able to see the time part.
For example:
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MISS' ;
to be able to see the time part. The time part is inherently stored along with the date field in case you feed the time.
HTH
Somnath
|
|
|
|
|
|
| Re: problem with date [message #127306 is a reply to message #127291] |
Mon, 11 July 2005 03:19 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
HI
U can view the selected date in any format that u specify in ur query.
For eg:
select to_char(d,'dd-MM-YYYY HH24:MI:SS') from t;
Also u can set the NLS_DATE_FORMAT parameter specifying the format that u wish to view.
Eg:
ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS' ;
Date is stored in data field in format defined by NLS. This includes the time as well.
Regds
Girish
|
|
|
|
|
|
|
|
|
|
| Re: problem with date [message #127414 is a reply to message #127287] |
Mon, 11 July 2005 10:16 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You don't have to use or display the date if you only are interested in the time. But both will be stored in the date field (and the timestampt field as well I believe). So just ignore the presence of the date, only use the time.
MYDBA@ORCL > select to_char(sysdate,'HH24:MI:SS') from dual;
TO_CHAR(
--------
11:19:22
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
