How do I format the Date variable when inserting into a database table using java

Question

I have this code to build a table:

CREATE TABLE Course
(
    CustomerName varchar(30) NOT NULL, Title varchar (30) NOT NULL,
    Type varchar(30) NOT NULL, Instructor varchar(30) NOT NULL, StartDate Date NOT NULL, EndDate Date NOT NULL,
    Price float NOT NULL
);

When I try to insert something into the Course table, the date says it is the wrong format. It either says the date cant hold integer or string. How do I enter the date so that it will except it? Below are all the ways I have tried:

INSERT INTO Course(CustomerName, Title, Type, Instructor, StartDate,EndDate,Price) VALUES ('Hary','Intro to Stupidity', 'Programming','Davis', 2017/11/12, 2017/04/03, 124.00);

INSERT INTO Course(CustomerName, Title, Type, Instructor, StartDate,EndDate,Price) VALUES ('Hary','Intro to Stupidity', 'Programming','Davis', 2017-11-12, 2017-04-03, 124.00);

INSERT INTO Course(CustomerName, Title, Type, Instructor, StartDate,EndDate,Price) VALUES ('Hary','Intro to Stupidity', 'Programming','Davis', 2017.11.12, 2017.04.03, 124.00);

INSERT INTO Course(CustomerName, Title, Type, Instructor, StartDate,EndDate,Price) VALUES ('Hary','Intro to Stupidity', 'Programming','Davis', (2017/11/12), (2017/04/03), 124.00);

If anyone could help that would be great!

Short answer, don't. Instead make use of PreapredStatements ability to handle date/time types directly and let the underlying driver deal with it - See Using Prepared Statements for more details — MadProgrammer
– MadProgrammer, Commented Oct 13, 2017 at 3:14
What database you use? Oracle, Postgre, Mysql ... etc. If you use Oracle, take look at this link: psoug.org/definition/to_date.htm — JHDev
– JHDev, Commented Oct 13, 2017 at 3:33
For postgre: techonthenet.com/postgresql/functions/to_date.php For MySQL : mysqltutorial.org/mysql-str_to_date — JHDev
– JHDev, Commented Oct 13, 2017 at 3:38
I am using Java, which is using Derby. That's as much as I know about the database. Also I have tried using the "" around the date and it stills gives me and error — codejam
– codejam, Commented Oct 13, 2017 at 3:40

Basil Bourque · Accepted Answer · 2017-10-14 01:32:49Z

1

Databases do not store date-time values with a "format".

But you can input date-time values represented as strings that do have a format. Pass a string in proper format expected by SQL (YYYY-MM-DD) such as "2017-11-12" using double-quote marks.

Or, better yet, pass date-time objects for values going into date-time columns.

 LocalDate localDate = LocalDate.of( 2017 , Month.NOVEMBER , 12 ) ;

Use a PreparedStatement to avoid SQL injection security risks.

myPreparedStatement.setObject( … , localDate ) ;

Retrieve as a date-time object.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

Read the Oracle Tutorials and search Stack Overflow. These topics have already been addressed many many times.

Tip: use all lowercase in your column names for maximum portability across databases.

edited Oct 14, 2017 at 1:32

answered Oct 13, 2017 at 5:00

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bryan Pendleton Over a year ago

As an alternative to setObject, some programmers find setDate and setTimestamp to be slightly easier to code.

Basil Bourque Over a year ago

@BryanPendleton The setDate and setTimestamp methods take arguments using classes that are poorly-designed, troublesome, and now legacy. Avoid them. Sun, Oracle, and the JCP community have all given up on those classes, and so should you. They are now supplanted by the java.time classes.

Bryan Pendleton Over a year ago

I'm not disputing that the newer JDK date/time classes are nice. I was just noting that setObject and getObject are rather generic JDBC interfaces, not quite as "friendly" as the setDate and setTimestamp methods. You are right, the core Java community has moved on to a new set of date/time classes. I'm not sure that enthusiasm has translated to the JDBC community as yet, however.

Collectives™ on Stack Overflow

How do I format the Date variable when inserting into a database table using java

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related