Insert date value in SQL table

Question

I have the following table :

EMPNO(number), ENAME(varchar), JOB(char), MGR(number), HIREDATE(date), SAL(number), DEPTNO(number)

I try to insert the following :

insert into EMP_TF1605(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
    values(7369, 'SMITH', 'CLERK', 7902, 17-DEC-1980, 800, 20);

Result : Error occured. Recheck your SQL statement

I am sure it is the date that is incorrect?

Answer 1

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

INSERT INTO EMP_TF1605(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES(7369,
       'SMITH',
       'CLERK',
       7902,
       '1980-12-17',
       800,
       20);

It will insert your data successfully in most 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 EMP_TF1605(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES(7369,
       'SMITH',
       'CLERK',
       7902,
       to_date('1980-12-17', 'yyyy-mm-dd'),
       800,
       20);

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.

INSERT INTO EMP_TF1605(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
VALUES(7369,
       'SMITH',
       'CLERK',
       7902,
       cast('17-Dec-1980' AS datetime),
       800,
       20);

For other rdbms, you need to look for the equivalent casting functions.

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('20-SEP-2017', 'DD-MON-YYYY')

You can check demos i.e. MySQL, PostgreSQL, SQL Server, Oracle