Problem with date insertion in MySQL

When i try to insert the Date in MySQL the format will be collapsed. i need dd-MM-yyyy format and the MySQL gives the output as yyyy-MM-dd...Here is my code...Please help me get out of the problem...

I don't see any code that retrieves dates from a DB, but the ResultSet.getDate method returns a Date object that can be formatted any way you want.

I don't see any code that retrieves dates from a DB, but the ResultSet.getDate method returns a Date object that can be formatted any way you want.

I am not retrieving any date.. i try to insert a date in MySQL where the field was already declared as DATE type.. But after storing the value and when i see it in DB it looks like this...."0029-13-12"...I don't know what exactly is going on....Please help me figure out what is the problem and how can i insert the date to MySQL in a proper format...

The easiest would be to use a PreparedStatement and its setDate method. That relieves you from having to format the date yourself.

Hi Ulf,
I already use the PreparedStatement for insert the date and yes, i use the setDate() method to insert the date into the table..but still the problem persists.. I want the date to be displayed as in dd-MM-yyyy format...

Originally posted by Rajkumar balakrishnan:
I want the date to be displayed as in dd-MM-yyyy format...

Then when you extract the date from the database, use a SimpleDateFormat object to convert it to a string in that format. That part of the question has nothing to do with the database at all.

Edit: Rereading the thread, I see that Ulf already said that two days ago.
[ July 14, 2008: Message edited by: Paul Clapham ]

When i use the below code an IllegalArgumentException is thrown in the console...

What is the actual value of "da" that you're trying to convert into a date? DATE_FORMAT is not compatible with the format expected by java.sql.Date.valueOf; see its javadocs for what it expects.

Try this while inserting into database.

<blockquote>code:

<pre name="code" class="core">
INSERT INTO yourtable (datefield) VALUES (str_to_date('01/02/2007', '%d/%m/%Y'));
</pre>

</blockquote>

so from java you will be passing the string in the date field your sql query will convert the string in to the format specified in the query. '%d/%m/%Y'

[ July 15, 2008: Message edited by: Jigar Naik ]
[ July 15, 2008: Message edited by: Jigar Naik ]

Hi Ulf,
the da is the SQLDATE format and i use it to pass the date string into MySQL... Are you clear!!!

the da is the SQLDATE format

It will be, once the code successfully converts the date to a string; but it fails to do that. From your earlier post:

SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
d = DATE_FORMAT.parse(date);
date = DATE_FORMAT.format(d);

java.sql.Date da = java.sql.Date.valueOf(date);

As I said, read the javadocs of Date.valueOf. "dd/MM/yyyy" is not a date format it understands.

Are you clear!!!

Is that meant to encourage me to help you? It achieves rather the opposite.

Is that meant to encourage me to help you? It achieves rather the opposite.

Sorry Ulf... I don't think like that but not going to think like that too!...
Thanks for your help and i try to out my best in the code.Ok..Once i insert the date into MySQL in the yyyy-MM-dd format, How can i retrieve it in the format that the user wants....
This will help me a lot...

My first post in this thread mentioned the method that retrieves a Date object from a ResultSet. Is that what you're asking?

Ulf, i need the sample code so i can easily understand.. Because i already have a code which looks like so messy and i can't even modify it as you told...Please help me...

Please somebody help me get rid of this problem.....

Hi Rajkumar,

I can give you solution but i as other ppl found your explanation not clear.. so please let me know each and every detail like what you have in java and what you have in mysql like data types both the sides ..
Please make it clear to i can help you...