How to Insert date value in SQL table

Question

Refer to the following table :

EMPID(number), FULLNAME(varchar), DESIGNATION(varchar), JOINING(date), SAL(number), DEPTNAME(varchar)

I try to insert the following :

insert into EMPLOYEE(EMPID, FULLNAME, DESIGNATION, JOINING, SAL, DEPTNAME)
    values(8976, 'JOHN', 'JOE', 'ANALYST', 12-DEC-1990, 30000, 'Analytics');

Result : Error occured. Recheck your SQL statement

Why is this error coming?

Answer 1

Always use ANSI default string literal format for date i.e. YYYY-MM-DD like below.

INSERT INTO EMPLOYEE (EMPID, FULLNAME, DESIGNATION, JOINING, SAL, DEPTNAME)
VALUES(8976,
       'JOHN',
       'JOE',
       'ANALYST',
       '1990-12-12',
       30000,
       'Analytics');

It will insert your data in RDBMS i.e. MySQL, PostgreSQL, SQL Server.

In Oracle, you need to convert it to date using function to_date([value],[format] prior to insertion as below.

INSERT INTO EMPLOYEE (EMPID, FULLNAME, DESIGNATION, JOINING, SAL, DEPTNAME)
VALUES(8976,
       'JOHN',
       'JOE',
       'ANALYST',
       to_date(1990-12-12', 'yyyy-mm-dd'),
       30000,
       'Analytics');

However if your input date is in format mentioned in question, you can use cast in SQL Server to convert it to datetime before insertion as below.

Update:

In Oracle, for the date format provided in question, you can use to_date to convert your string date literal input along using format 'DD-MON-YYYY' to data type date.

TO_DATE('14-SEP-2000', 'DD-MON-YYYY')

If you need to know more about SQL, it is recommended to go for the SQL Online Course today.

Comments

Very helpful, thanks!!!