Oracle — separate time and date

2018-06-12 22:12:27

I have a question with Oracle (I've installed Oracle 11g Express Edition). I want to insert values for 'date' and 'time', but I cannot separate them.

create table Match 
(
    numMatch number(2) constraint PKMatch primary key,
    dateM date, 
    heureM date,
    numE_Eq number(2),
    numE_Eq2 number(2),
    nomTerrain varchar2(30)
);

--"tools"=>"preferences"=>"format de date:DD/MM/YYYY HH24:MI:SS"

insert into Match values (1,to_date
('10/12/2010','DD/MM/YYYY'),to_date('15:00:00','HH24:MI:SS'),1,3,'Stade Argentina'
);

result:

dateM: 10/12/2010 00:00:00

heureM: 01/11/2012 15:00:00

PS: I've tried to_char instead of to_date, but it didn't work at all.

Yes, I'm aware of that 'DATE datatype contains both date and time', but it's the prof who insists showing date and time separately in the table, and I've seen your solutions before, but for me, it's a query, not to 'insert values' in the table.

So I'd like to know how I can have a table directly presenting date and time.

Oracle doesn't have a TIME datatype. You can store a DATE with a time component, and just query based on time, and display based on time.

select to_char(my_date_field, 'HH24:MI:SS') 
from my_table
where to_date(my_date_field, 'HH24:MI') = '18:51';

Alternatively, you can store seconds from midnight as an integer, and calculate the time of day from that. It will also make querying for range times easier I think.

Also, within a session, execute the following to have all dates formatted the way you wish:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

Oracle DATE type includes both DATE and TIME information (because it pre-dates the SQL-92 standard when the standard DATE, TIME and TIMESTAMP types were added). So, you can't separate them in the table; there's no reason to do so, either. You can, if you so desire, create a view which presents the DATE field as separate date-only and time-only display fields.

Another way to represent a TIME equivalent type in Oracle is with the INTERVAL type, such as:

SQL> CREATE TABLE foo (
       bar INTERVAL DAY(0) TO SECOND(3)
     );

This would allow the storage of a time period with 0 precision of the DAY component, and 3 decimal points for the SECOND component. An INSERT example is:

SQL> INSERT INTO foo VALUES ('0 01:01:01.333');

What's great about this approach is that it automatically presents the results of a SELECT in an intuitive format without the need for conversion:

SQL> SELECT * FROM foo;

BAR
---------------------------------------------------------------------------
+0 01:01:01.333

链接地址: http://www.djcxy.com/p/36910.html

上一篇: 错误日期格式C＃，Oracle

下一篇: Oracle - 单独的时间和日期