How to Insert date value in SQL table

+1 vote

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?

Feb 17, 2022 in Database by Vaani
• 7,070 points
175,471 views

1 answer to this question.

+1 vote

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.

answered Feb 17, 2022 by Neha
• 9,060 points

Related Questions In Database

0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
25,982 views
0 votes
1 answer

How to insert date values into table?

You have to convert the literal to ...READ MORE

answered Feb 18, 2022 in Database by Vaani
• 7,070 points
3,070 views
0 votes
0 answers

How to convert date into timestamp in SQL query?

I'm attempting to move data from the ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,020 points
1,595 views
0 votes
0 answers

How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows: Emp_cd ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,020 points
1,540 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17, 2022 in Database by Vaani
• 7,070 points
729 views
0 votes
0 answers

Left Join With Where Clause

I need to take all of the ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,020 points
582 views
0 votes
1 answer

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing. Consider these ...READ MORE

answered Sep 16, 2022 in Database by narikkadan
• 63,720 points
984 views
0 votes
1 answer

Which query to use for better performance, join in SQL or using Dataset API?

DataFrames and SparkSQL performed almost about the ...READ MORE

answered Apr 19, 2018 in Apache Spark by kurt_cobain
• 9,390 points
1,738 views
0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,060 points
713 views
0 votes
1 answer

How Can I use "Date" Datatype in sql server?

There's problem in all of them and ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,060 points
1,298 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP