Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Date/Time Problem

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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').
 
That is a really big piece of pie for such a tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic