oracle - How to INSERT a DATE? -
hi new oracle , forgot how enter date. have tried many variations , can't seem it, plus have looked on internet can't find correct example. table set this: hiredate date, (date being assume default input, default).
thanks help.
here's code:
sql> set linesize 120 sql> set pagesize 45 sql> set feedback 1 sql> set echo on sql> sql> drop table employee 2 sql> creat table student sp2-0734: unknown command beginning "creat tabl..." - rest of line ignored. sql> create table employee 2 ( 3 empno number(4)constraint employee_empno_pk primary key, 4 empname varchar(10), 5 job varchar(10), 6 manager number(4), 7 hiredate date, 8 salary number(7,2), 9 commission number(7,2), 10 deptno number(2) 11 ); table created. sql> insert employee 2 values(7839, 'president', null, 11/17/1981, 5000, null, 10); insert employee * error @ line 1: ora-00947: not enough values sql> values(7839, 'president', null, '11/17/1978', 5000, null, 10); sp2-0734: unknown command beginning "values(783..." - rest of line ignored. sql> insert employee 2 values(7839, 'president', null, '11/17/1978', 5000, null, 10); insert employee * error @ line 1: ora-00947: not enough values sql> insert employee 2 values(7839, 'king', 'president', null, 11/17/1981, 5000, null, 10); values(7839, 'king', 'president', null, 11/17/1981, 5000, null, 10) * error @ line 2: ora-00932: inconsistent datatypes: expected date got number sql> insert employee 2 values(7839, 'king', 'president', null, 19811117, 5000, null, 10); values(7839, 'king', 'president', null, 19811117, 5000, null, 10) * error @ line 2: ora-00932: inconsistent datatypes: expected date got number sql> insert employee 2 values(7839, 'king', 'president', null, '11/17/1981', 5000, null, 10); values(7839, 'king', 'president', null, '11/17/1981', 5000, null, 10) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king', 'president', null, '1978/11/17 3 ) 4 ; error: ora-01756: quoted string not terminated sql> insert employee 2 values(7839, 'king', 'president' null, '1978/11/17', 5000, null, 10); values(7839, 'king', 'president' null, '1978/11/17', 5000, null, 10) * error @ line 2: ora-00917: missing comma hello, new mysql , forgot how insert @ date when using mysql plus. have tried many different variations able see , can't seem figure out. looked on internet find examples make there own formats. correct way insert date when table created using: hiredate sql> insert employee 2 values(7839, 'king', 'president', null, '1981/11/17', 5000, null, 10); values(7839, 'king', 'president', null, '1981/11/17', 5000, null, 10) * error @ line 2: ora-01861: literal not match format string sql> insert employee 2 values(7839, 'king', 'president', null, '1981/17/11', 5000, null, 10); values(7839, 'king', 'president', null, '1981/17/11', 5000, null, 10) * error @ line 2: ora-01861: literal not match format string sql> insert employee 2 values(7839, 'king', 'president', null, '17/11/1981', 5000, null, 10); values(7839, 'king', 'president', null, '17/11/1981', 5000, null, 10) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king', 'president', null, '81/11/17', 5000, null, 10); values(7839, 'king', 'president', null, '81/11/17', 5000, null, 10) * error @ line 2: ora-01847: day of month must between 1 , last day of month sql> insert employee 2 values(7839, 'king', 'president', null, '17/11/81', 5000, null, 100); values(7839, 'king', 'president', null, '17/11/81', 5000, null, 100) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king, 'president', null, '11/17/1981', 5000, null, 10); error: ora-01756: quoted string not terminated sql> insert employee 2 values(7839, 'king', 'president', null, '11/17/81', 5000, null, 10); values(7839, 'king', 'president', null, '11/17/81', 5000, null, 10) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king', 'president', null, '17/11/81', 5000, null, 10); values(7839, 'king', 'president', null, '17/11/81', 5000, null, 10) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king', 'president', null, '81/17/11', 5000, null, 10); values(7839, 'king', 'president', null, '81/17/11', 5000, null, 10) * error @ line 2: ora-01847: day of month must between 1 , last day of month sql> insert employee 2 values(7839, 'king', 'president', null, '17/81/11', 5000, null, 10); values(7839, 'king', 'president', null, '17/81/11', 5000, null, 10) * error @ line 2: ora-01843: not valid month sql> insert employee 2 values(7839, 'king', 'president', null, '81/11/17', 5000, null, 10); values(7839, 'king', 'president', null, '81/11/17', 5000, null, 10) * error @ line 2: ora-01847: day of month must between 1 , last day of month sql> insert employee 2 values(7839, 'king', 'president', null, '1981-11-17', 5000, null, 10); values(7839, 'king', 'president', null, '1981-11-17', 5000, null, 10) * error @ line 2: ora-01861: literal not match format string sql> insert employee 2 values(7839, 'king', 'president', null, '17/11/1981', 5000, null, 10); values(7839, 'king', 'president', null, '17/11/1981', 5000, null, 10) * error @ line 2: ora-01843: not valid month
you can use to_date function:
to_date('11/17/1981', 'mm/dd/yyyy') it lets specify date string , format in oracle can convert date.
or can use date literal:
date '1981-11-17'
Comments
Post a Comment