Home » SQL & PL/SQL » SQL & PL/SQL » How to insert only the time value in the date column
How to insert only the time value in the date column [message #185184] Mon, 31 July 2006 07:46 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi

How to insert only the time value in the date column

Thanks
Re: How to insert only the time value in the date column [message #185185 is a reply to message #185184] Mon, 31 July 2006 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No way. Insert date and time, but select whatever you want (time in your case) using TO_CHAR function, such as

SELECT TO_CHAR(date_column, 'hh24:mi:ss') FROM your_table;

and display it wherever needed.
Re: How to insert only the time value in the date column [message #185188 is a reply to message #185184] Mon, 31 July 2006 07:53 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Even if you try to insert only time value...

create table 
table_1 (date_1 date);

insert into table_1 values (to_date('23:21:21','HH24:MI:SS'));


It takes date field from sysdate and stores both date and time.

Naveen
Re: How to insert only the time value in the date column [message #185191 is a reply to message #185184] Mon, 31 July 2006 08:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Date field can be used to store the Time Value ... Even then it will be having Date Portion also ..

SQL> insert into t3 values(To_date('10:25','HH24:MI'));

1 row created.

SQL> alter session set nls_date_format='HH24:Mi:SS';

Session altered.

SQL> select * from t3;

DAT
--------
10:25:00

SQL> insert into t3 (dat) values(to_date('10:10:10','HH24:MI:SS'));

1 row created.

SQL> select * from t3;

DAT
--------
10:25:00
10:10:10

SQL> insert into t3 values(To_date('20:25','HH24:MI'));

1 row created.

SQL> select * from t3;

DAT
--------
10:25:00
10:10:10
20:25:00

SQL> alter session set nls_date_format='DD-MM-YYYY HH24:Mi:SS';

Session altered.

SQL> select * from t3;

DAT
-------------------
01-07-2006 10:25:00
01-07-2006 10:10:10
01-07-2006 20:25:00


You have to Filter the date field while retrieving the data.

Or You can Try for another Varhar2 field exclusively for Exclusive time storage.

Thumbs Up
Rajuvan
Re: How to insert only the time value in the date column [message #185196 is a reply to message #185191] Mon, 31 July 2006 08:08 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
hi


i want to insert the time value as follows..
'12:00:00 AM'

How can i do this..

Help on this is greatly appreciated..

Thanks
Re: How to insert only the time value in the date column [message #185204 is a reply to message #185196] Mon, 31 July 2006 08:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Just use TRUNC(whatever date you want) into the column.
Re: How to insert only the time value in the date column [message #185228 is a reply to message #185191] Mon, 31 July 2006 11:32 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rajavu1 wrote on Mon, 31 July 2006 15:01

Or You can Try for another Varhar2 field exclusively for Exclusive time storage.

I wouldn't do that; there's really no need for it. This column is practically unusable (you can't compare it to anything, do arithmetic ... nothing, without unnecessary pain). If there's a DATE column and additional VARCHAR2 one just for time portion, that would be really crazy; there was discussion about how many storage NULL column takes (a lot! One wouldn't believe!), so what would you do with something you can easily extract from the DATE column?

No matter what, I really wouldn't want to have a VARCHAR2 column for DATE values.

Until someone shows me that this, actually, can bring some benefit.
Previous Topic: where current of
Next Topic: SQL CHALLENGE TO THE EXPERTS OUT THERE
Goto Forum:
  


Current Time: Sat Dec 10 10:42:39 CST 2016

Total time taken to generate the page: 0.07784 seconds