aspose file tools*
The moose likes JDBC and the fly likes Date/Time Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Date/Time Problem" Watch "Date/Time Problem" New topic
Author

Date/Time Problem

Peter Harvey
Greenhorn

Joined: May 28, 2001
Posts: 13
Hi, Everyone.
I have the following problem: I am trying to insert data into a table called Schedule where the fields are Date, Team, and Time.
Date has to be formatted like 6/18/01, Team is a Number, and Time has to be formatted like 6:55 PM. My sql looks like this:
Insert into Schedule (Date, Team, Time) values (6/18/01, 10, 6:55 )
That's taken directly from the screen (I print out all my queries before they're executed). I can put single quotes (') around the date and time, leave 'em off one, put 'em on the other, etc., etc., but no possible combination seems to work. I also tried adding the PM to the time, both with and without ' ' , but no go.
What am I doing wrong?
Julio Lopez
Greenhorn

Joined: Nov 14, 2000
Posts: 28
What am I doing wrong?
Try the date escape format
'yyyy-mm-dd' for the date
'hh:mm:ss' for the time
For PM times use military time for the hour field.
Post back if this does not work out.
Julio Lopez
M-Group Systems
Peter Harvey
Greenhorn

Joined: May 28, 2001
Posts: 13
Nope. Insert into Schedule (Date, Team, Time) values ('2001-5-6', 10, '6:55:00') still produces
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
Still open to suggestions ...
Julio Lopez
Greenhorn

Joined: Nov 14, 2000
Posts: 28

Still open to suggestions ...
Another thing you could try is to use a PreparedStatement instead of the standard Statement.
Connection con = null;
PreparedStatement prepStatement = null;
String theSQLString = "Insert into Schedule (Date, Team, Time) values (?, ?, ?)";
// Register driver
Class.forName(driver).newInstance();
// Get connection, create SQL statement and execute it
con = DriverManager.getConnection();
prepStatement = con.prepareStatement(theSQLString);
// Date and Time must be java.sql.Date and java.sql.Time objects
prepStatement.setDate(1, formattedDate);
prepStatement.setInt(2, Team);
prepStatement.setTime(3, formattedTime);
prepStatement.executeUpdate();

To get the Date and Time objects I would do the following,
java.util.Calendar theCalendar = java.util.Calendar.getInstance();
// 6/18/01, 10, 6:55 method set(int year, int month, int day, int hour, int minute, int second), also month = actual month - 1
theCalendar.set(2001, 5, 18, 6, 55, 00);
long timeInMillis = theCalendar.getTime().getTime();
java.sql.Date formattedDate = new java.sql.Date(timeInMillis);
java.sql.Time formattedTime = new java.sql.Time(timeInMillis);
This should work if not then post back.
Julio Lopez
M-Group Systems
Peter Harvey
Greenhorn

Joined: May 28, 2001
Posts: 13
Nothing works. I tried using a prepared statment, tried using Date and Time objects without the prepared statement, still no go. My sql looks fine to me, but I still get the same error. The Date field in MS Access is Date/Time(Short Date) and the Time field is Date/Time(Medium Time).
I'm at the point now where I'm probably just going to make three fields for Date (month, day, and year), and the same for Time (hour, minute, second), all ints. I probably should have done this first, but I wanted to learn the proper way to do it. Guess it serves me right.
Thanks for the Calendar lesson, though. I learned a lot from it.
You can still take another crack at my problem if you want.
Peter Harvey
Greenhorn

Joined: May 28, 2001
Posts: 13
FINALLY!!
I figured it out! It turns out that you're not allowed to name a column 'Date' in MS-Access. I just renamed the column, and everything works fine.
I swear, I almost cried when I figured it out. And all this time I figured it was a Java problem.
Excuse me while I glue back the hair which has been torn out over the last two days...
Paul Bailey
Ranch Hand

Joined: Oct 20, 2000
Posts: 91
Originally posted by Peter Harvey:
FINALLY!!
I figured it out! It turns out that you're not allowed to name a column 'Date' in MS-Access. I just renamed the column, and everything works fine.

Actually, you can, but you have to call it '[date]', not 'date' as you were. i.e.
Insert into Schedule ([Date], Team, Time) values ('2001-5-6', 10, '6:55:00').
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Date/Time Problem