How do you insert a date in a table?

user_13501438 0
Viewed 265

I have this table but I'm not sure what is the format for entering dates

insert into Works (ClientID, CCode, BranchNo, EquipNo, HireDate)
select 001, 101, 01, 24500, '23-JAN-13' from dual union
select 002, 102, 01, 23200, '12-SEP-12' from dual union
select 003, 103, 01, 11500, '15-DEC-14' from dual union
select 004, 104, 01, 76830, '16-MAR-16' from dual union
select 005, 105, 01, 23760, '08-JUN-15' from dual

This doesn't work, it just gives me this error:

ORA-00904: "HIREDATE": invalid identifier

New error: https://m.imgur.com/gallery/LkrLsol

oracle-apex-5 oracle-apex-5.1 date oracle sql
2 Answers

Try using valid date literals:

insert into Works (ClientID, CCode, BranchNo, EquipNo, HireDate)
select 001, 101, 01, 24500, date '2013-01-23' from dual union
select 002, 102, 01, 23200, date '2012-09-12' from dual union
select 003, 103, 01, 11500, date '2014-12-15' from dual union
select 004, 104, 01, 76830, date '2016-03-16' from dual union
select 005, 105, 01, 23760, date '2015-06-08' from dual

You could also use Oracle's TO_DATE function:

TO_DATE('23-JAN-13', 'DD-MON-YY')
user_18632290

'23-JAN-13' is not a date, it is a string. Avoid using two digit year 'YY'. That's the reason Y2K bug started.

To insert DATE values:

Use TO_DATE with appropriate format mask:

TO_DATE('09-05-2020', 'DD-MM-YYYY')

Or,

ANSI Date literal, it uses fixed format 'YYYY-MM-DD':

DATE '2020-05-09'
user_39896080
Related