Insert date value in SQL table

0 votes

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 in Database by Vaani
• 5,040 points
41 views

1 answer to this question.

0 votes

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')
answered Feb 17 by Neha
• 6,260 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
15,401 views
0 votes
1 answer

How to insert date values into table?

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

answered Feb 18 in Database by Vaani
• 5,040 points
64 views
0 votes
1 answer

SQL count rows in a table

A fast way of doing this would ...READ MORE

answered Feb 21 in Database by Neha
• 6,260 points
71 views
0 votes
1 answer

Convert Date format into DD/MMM/YYYY format in SQL Server

Using convert() and style 106, you can ...READ MORE

answered Feb 23 in Database by Vaani
• 5,040 points
749 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 in Database by Vaani
• 5,040 points
31 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
929 views
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by Parth
• 4,630 points
850 views
0 votes
1 answer
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 in Database by Neha
• 6,260 points
45 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 in Database by Neha
• 6,260 points
53 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP