I have a field that gives me time and it is expressed like this :
我有一个给我时间的字段,它表达如下:
7:00 AM
the problem is that when i try to add it to the column that should contain time it gives me error because the format is wrong.
问题是,当我尝试将它添加到应该包含时间的列时,它会给我错误,因为格式错误。
I tried to change the format using to_char but it is giving me an error saying that the function is not implemented.
我试图使用to_char更改格式,但它给我一个错误,说明该函数未实现。
here is the code.
这是代码。
CREATE TABLE call_info
(
call_id NUMBER(4)
CONSTRAINT cio_call_id_CK CHECK (call_id >= 1000 AND call_id <= 9999),
report_first VARCHAR2(10),
report_last VARCHAR2(11) CONSTRAINT cio_report_last_NN NOT NULL,
report_date DATE CONSTRAINT cio_report_date_NN NOT NULL,
report_time DATE CONSTRAINT cio_report_time_NN NOT NULL,
problem_code NUMBER(1) CONSTRAINT cio_problem_code_NN NOT NULL,
service_code VARCHAR2(4),
CONSTRAINT cio_call_id_PK PRIMARY KEY (call_id),
CONSTRAINT cio_problem_code_FK FOREIGN KEY (problem_code)
REFERENCES problems(code),
CONSTRAINT cio_service_code_FK FOREIGN KEY (service_code)
REFERENCES services(code)
)
;
BEGIN
INSERT INTO call_info
VALUES ('1102','Bill','Madden',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('7:00 PM','hh:mi AM'),'hh:mi-AM'),'2','PSEG');
INSERT INTO call_info
VALUES ('1103','Anita','Verno',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('7:01 PM','hh:mi AM'),'hh:mi-AM'),'2','PSEG');
INSERT INTO call_info
VALUES ('1200','Emily','Vandalovsky',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('7:45 PM','hh:mi AM'),'hh:mi-AM'),'2','PSEG');
INSERT INTO call_info
VALUES ('1111','Gary','Correa',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('8:10 PM','hh:mi AM'),'hh:mi-AM'),'1','DPR');
INSERT INTO call_info
VALUES ('1101','Mickey','Mouse',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('11:00 PM','hh:mi AM'),'hh:mi-AM'),'6','PSEG');
INSERT INTO call_info
VALUES ('1012','Minnie','Mouse',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('11:21 PM','hh:mi AM'),'hh:mi-AM'),'1','DPR');
INSERT INTO call_info
VALUES ('1013','Goofy','Disney',TO_DATE('29-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('11:47 PM','hh:mi AM'),'hh:mi-AM'),'5','OEM');
INSERT INTO call_info
VALUES ('1040','Donald','Duck',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('2:34 AM','hh:mi AM'),'hh:mi-AM'),'4','OEM');
INSERT INTO call_info
VALUES ('1501','Cinderella','Disney',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('3:15 AM','hh:mi AM'),'hh:mi-AM'),'3','CSH');
INSERT INTO call_info
VALUES ('1506','Ernie','Sesame',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('3:16 AM','hh:mi AM'),'hh:mi-AM'),'3','CSH');
INSERT INTO call_info
VALUES ('1007','Burt','Sesame',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('3:18 AM','hh:mi AM'),'hh:mi-AM'),'3','CSH');
INSERT INTO call_info
VALUES ('1081','Bruce','Springsteen',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('4:10 AM','hh:mi AM'),'hh:mi-AM'),'2','PSEG');
INSERT INTO call_info
VALUES ('1910','Chris','Christie',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('4:25 AM','hh:mi AM'),'hh:mi-AM'),'7','OEM');
INSERT INTO call_info
VALUES ('1010','Mitt','Romney',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('5:15 AM','hh:mi AM'),'hh:mi-AM'),'1','DPR');
INSERT INTO call_info
VALUES ('1015','Barack','Obama',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('5:17 AM','hh:mi AM'),'hh:mi-AM'),'1','DPR');
INSERT INTO call_info
VALUES ('1019','Bruce','Wayne',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('7:57 AM','hh:mi AM'),'hh:mi-AM'),'5','OEM');
INSERT INTO call_info
VALUES ('1314','Minas','Kousoulis',TO_DATE('30-OCT-17','DD-MON-YY'),TO_CHAR(TO_DATE('8:01 AM','hh:mi AM'),'hh:mi-AM'),'4','WTR');
END;
Please note this the date datatype cannot be changed to a different one.
请注意,date数据类型不能更改为其他数据类型。
Thank you for the attention.
谢谢你的关注。
Danilo
1 个解决方案
#1
0
Why are you storing the time in a separate field? Are you aware that DATE
has a "time" component? Separating the fields almost never makes sense:
为什么要将时间存储在单独的字段中?你知道DATE有一个“时间”组件吗?分离字段几乎没有意义:
CREATE TABLE call_info (
call_id NUMBER(4)
CONSTRAINT cio_call_id_CK CHECK (call_id >= 1000 AND call_id <= 9999),
report_first VARCHAR2(10),
report_last VARCHAR2(11) CONSTRAINT cio_report_last_NN NOT NULL,
report_datetime DATE CONSTRAINT cio_report_date_NN NOT NULL,
problem_code NUMBER(1) CONSTRAINT cio_problem_code_NN NOT NULL,
service_code VARCHAR2(4),
CONSTRAINT cio_call_id_PK PRIMARY KEY (call_id),
CONSTRAINT cio_problem_code_FK FOREIGN KEY (problem_code)
REFERENCES problems(code),
CONSTRAINT cio_service_code_FK FOREIGN KEY (service_code)
REFERENCES services(code)
);
Then I would suggest inserting values using Oracles TIMESTAMP
keyword:
然后我建议使用Oracles TIMESTAMP关键字插入值:
INSERT INTO call_info (call_id, report_first, report_last, report_datetime, problem_code, service_code)
VALUES (1102, 'Bill', 'Madden', TIMETAMP '2017-10-29 19:00:00',
2, 'PSEG');
Note that I also removed the single quotes around the numbers and the insert
lists all the columns.
请注意,我还删除了数字周围的单引号,插入列出了所有列。
#1
0
Why are you storing the time in a separate field? Are you aware that DATE
has a "time" component? Separating the fields almost never makes sense:
为什么要将时间存储在单独的字段中?你知道DATE有一个“时间”组件吗?分离字段几乎没有意义:
CREATE TABLE call_info (
call_id NUMBER(4)
CONSTRAINT cio_call_id_CK CHECK (call_id >= 1000 AND call_id <= 9999),
report_first VARCHAR2(10),
report_last VARCHAR2(11) CONSTRAINT cio_report_last_NN NOT NULL,
report_datetime DATE CONSTRAINT cio_report_date_NN NOT NULL,
problem_code NUMBER(1) CONSTRAINT cio_problem_code_NN NOT NULL,
service_code VARCHAR2(4),
CONSTRAINT cio_call_id_PK PRIMARY KEY (call_id),
CONSTRAINT cio_problem_code_FK FOREIGN KEY (problem_code)
REFERENCES problems(code),
CONSTRAINT cio_service_code_FK FOREIGN KEY (service_code)
REFERENCES services(code)
);
Then I would suggest inserting values using Oracles TIMESTAMP
keyword:
然后我建议使用Oracles TIMESTAMP关键字插入值:
INSERT INTO call_info (call_id, report_first, report_last, report_datetime, problem_code, service_code)
VALUES (1102, 'Bill', 'Madden', TIMETAMP '2017-10-29 19:00:00',
2, 'PSEG');
Note that I also removed the single quotes around the numbers and the insert
lists all the columns.
请注意,我还删除了数字周围的单引号,插入列出了所有列。