data handling

[Deyna Cegielski]

my mysql database stored dates as strings in the format dd/mm/yyyy.

im having problems inserting into the database because the values i want to insert for the date are from a jformattedtextfield with simpledateformat("dd/mm/yy). i get a haunch of errors because of this.

any ideas how to get around this?

thanks

[stu derby]

Originally posted by Deyna Cegielski:
my mysql database stored dates as strings in the format dd/mm/yyyy.

im having problems inserting into the database because the values i want to insert for the date are from a jformattedtextfield with simpledateformat("dd/mm/yy). i get a haunch of errors because of this.

any ideas how to get around this?

thanks

Are you saying that you are storing date information into a column of string type? If so, that's a big part of your problem. Use an appropriate date or time type:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html

Or are you saying that you're using a date type and when you send MySQL a string that represents a date, it can't convert it? That's a different problem. You shouldn't rely on a database to do implicit type conversion for you; they all do it a little differently for one thing. Instead, first convert your date string to a java.util.Date in Java. Then second, use a PreparedStatement, and the setDate method.

[Deyna Cegielski]

basically i have a numbner of textfields which are dates (ive written my own class to check to format of the input to make sure its in the correct format). the db only needs to store the date doesnt really have much else to do with it hence why i chose a string. the user interface however has some comparisions to do between dates.

my way of thinking was to get the date from the db, cast it as a date object, then perform the necessary comparisons. would this work?

[stu derby]

Originally posted by Deyna Cegielski:
basically i have a numbner of textfields which are dates (ive written my own class to check to format of the input to make sure its in the correct format). the db only needs to store the date doesnt really have much else to do with it hence why i chose a string. the user interface however has some comparisions to do between dates.

my way of thinking was to get the date from the db, cast it as a date object, then perform the necessary comparisons. would this work?

It can work, but it's the hard way to do it. Java already has classes that will convert a text string to a date, and will throw an exception if the date doesn't fit the format. Sorting and searching based on dates in the database is either a lot slower or horribly slower and harder to code, depending on your choice of date format. The date data in the database is probably 8 to 10 times bigger than it needs to be, which also slows everything down. And there's lots more.

There are DATE datatypes for a reason and they should be used. Using a text column to store a date or a number value is almost always wrong.