Home » SQL & PL/SQL » SQL & PL/SQL » date display (10.2.0.1, linux)
date display [message #343860] Thu, 28 August 2008 06:31 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
How to view date data as

28-AUG-2008 03:15:32

I created a table
alter session set nls_date_format='dd-MON-YY hh24:mi:ss';
create table test (dt date);
Insert into test values('28-AUG-08');
select * from test;
28-AUG-08 00:00:0

How to track the hour,minutes and seconds?
Re: date display [message #343862 is a reply to message #343860] Thu, 28 August 2008 06:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
By simply inserting them?

Also, always use explicit conversion to date, don't rely on implicit conversions. They tend to bite you sooner or later.

Insert into test values(to_date('28-AUG-08 19:20:14','dd-MON-YY hh24:mi:ss'));
Re: date display [message #343873 is a reply to message #343860] Thu, 28 August 2008 06:51 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Where does oracle store the hours,minutes and seconds?
If I inserted a record in table t
insert into t select sysdate from dual;
after half an hour,i inserted again
insert into t select sysdate from dual;

Now if query table t,how could i see the values as
28-AUG-08 03:12:10
28-AUG-08 03:42:10
What option of oracle would display the dates along with hours,minutes and seconds?
Re: date display [message #343880 is a reply to message #343860] Thu, 28 August 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varu123,

More than 700 posts and you still don't know how to post:
- format it
- choose appropriate forum, do you think that SQL question is a question for Server Administration forum?

Quote:
Insert into test values('28-AUG-08');

You inserted only a date, no time => 00:00:00

Quote:
What option of oracle would display the dates along with hours,minutes and seconds?

TO_CHAR is the correct way to display date/time.

Regards
Michel
Re: date display [message #343883 is a reply to message #343860] Thu, 28 August 2008 07:05 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
700 posts!!
I didn't notice that.

I can use to_char function but why Oracle is not storing the hh:mi:ss by default?
Re: date display [message #343888 is a reply to message #343883] Thu, 28 August 2008 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why Oracle is not storing the hh:mi:ss by default?

Because it is its definition of DATE.

Regards
Michel
Re: date display [message #343892 is a reply to message #343860] Thu, 28 August 2008 07:18 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Do i need to change the datatype from date to timestamp?
Re: date display [message #343894 is a reply to message #343892] Thu, 28 August 2008 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varu123 wrote on Thu, 28 August 2008 14:18
Do i need to change the datatype from date to timestamp?

For what?
Remember that datatype are described in SQL Reference.

Regards
Michel

Re: date display [message #343896 is a reply to message #343860] Thu, 28 August 2008 07:24 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member

>>For what?

To see the hh24:mi:ss by default.

I need to compare daily data on the basis of dates.
Re: date display [message #343898 is a reply to message #343896] Thu, 28 August 2008 07:26 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
varu123 wrote on Thu, 28 August 2008 07:24

To see the hh24:mi:ss by default.

I need to compare daily data on the basis of dates.


This information is already stored as a part of the Date data-type. You did not see it in your first example because you inserted a date with no hours/minutes/seconds. If you don't insert the data you should not expect the DB to just assume you meant to and make it up for you.
Re: date display [message #343900 is a reply to message #343883] Thu, 28 August 2008 07:27 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member

varu123,
Oracle Does store Time in Date provided you Input the value to it.

Oracle date data type is capable of storing
Century,Year,Month,day,hour,minute,second.

as Michel said,
just do a to_char(date,date_format_needed)


You need may want to use timestamp DType incase you want to store fractional seconds.
Re: date display [message #343903 is a reply to message #343860] Thu, 28 August 2008 07:31 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So instead of sysdate I need to insert systimestamp to date.

insert into t select systimestamp from dual;
Re: date display [message #343904 is a reply to message #343903] Thu, 28 August 2008 07:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No. Sysdate has a time component to it.
SQL> create table test_0069(col_1 date);

Table created.

SQL> insert into test_0069 values (sysdate);

1 row created.

SQL> select to_char(col_1,'dd-mm-yyyy hh24:mi:ss') from test_0069;

TO_CHAR(COL_1,'DD-M
-------------------
28-08-2008 13:36:27
Re: date display [message #344302 is a reply to message #343883] Fri, 29 August 2008 05:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
varu123 wrote on Thu, 28 August 2008 13:31

Insert into test values('28-AUG-08');
select * from test;
28-AUG-08 00:00:0




varu123 wrote on Thu, 28 August 2008 14:05
I can use to_char function but why Oracle is not storing the hh:mi:ss by default?


Exactly how should Oracle know what time to add to the date of 28 August 2008 you provide?!
Previous Topic: Dynamic where condition
Next Topic: Stored Procedure help
Goto Forum:
  


Current Time: Sat Dec 10 16:31:03 CST 2016

Total time taken to generate the page: 0.06123 seconds