Home » SQL & PL/SQL » SQL & PL/SQL » oracle datatye
oracle datatye [message #263210] Wed, 29 August 2007 07:05 Go to next message
Messages: 6
Registered: February 2007
Location: Bangalore
Junior Member


i want to store only time in a databse table
i dont know which datatype i should use.
i used date so it storing by default date also

help me how to do that

Re: oracle datatye [message #263211 is a reply to message #263210] Wed, 29 August 2007 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What kind of time do you want to store.

Is it a time in a day? In this case date/timestamp are the correct datatypes (think if I give an appointment at 10AM, we likely never meet).

Is it a duration? Then use one of the INTERVAL datatypes.

Re: oracle datatye [message #263339 is a reply to message #263211] Wed, 29 August 2007 14:36 Go to previous message
Messages: 20849
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As Michel already told you (and as you already do it), DATE datatype can be used for such a purpose. I *think* I know what bothers you; now it is 29.08.2007. 21:15 CET. Function which returns it in Oracle is called SYSDATE. So, if you store this value into a table, you'd store both date and time:
SQL> create table test (col date);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> select * from test;

29.08.2007 21:16:44

What you'd like to do is to store ONLY time, so that result of a SELECT statement would be

The right way to do it is a few lines above. You have to store date and time, but you can choose what to select (i.e. display to the end user) using TO_CHAR function with desired format. For example:
SQL> select to_char(col, 'dd.mm.yyyy') result from test;


SQL> select to_char(col, 'hh24:mi') result from test;

There IS a way to store only time, as you'd like it to do, but in that case CHARACTER (or NUMBER) datatype should be used.
SQL> alter table test add col_c varchar2(5);

Table altered.

SQL> alter table test add col_n number(4);

Table altered.

SQL> update test set
  2    col_c = to_char(sysdate, 'hh24:mi'),
  3    col_n = to_number(to_char(sysdate, 'hh24mi'));

1 row updated.

SQL> select * from test;

COL                 COL_C      COL_N
------------------- ----- ----------
29.08.2007 21:16:44 21:24       2124

As you can see, 'col_c' and 'col_n' look like time values (to a human like you or me), but to Oracle it is a string or a number. Such an approach would bring your only tears, sweat and blood in future. Every statement you write would have to take care about true column meaning; every time you'd have to convert character or numeric value into a date (how would you do that? You have only time!); developers who'd inherit your solution would probably curse the day you decided to do it that way.

Simply, don't do that. DATE (or timestamp) datatype is used to store date AND time. Full stop.
Previous Topic: Grouping
Next Topic: Weird problem :(
Goto Forum:

Current Time: Sat Oct 22 04:22:45 CDT 2016

Total time taken to generate the page: 0.09543 seconds