aspose file tools*
The moose likes JDBC and the fly likes Problem with date insertion in MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with date insertion in MySQL" Watch "Problem with date insertion in MySQL" New topic
Author

Problem with date insertion in MySQL

Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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...



Never try to be a hard-worker. Be a smart-worker.
My Blog
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42929
    
  68
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.
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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...
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42929
    
  68
The easiest would be to use a PreparedStatement and its setDate method. That relieves you from having to format the date yourself.
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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...
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18988
    
    8

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 ]
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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



Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42929
    
  68
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.
Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 759
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 ]

Jigar Naik


Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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

Joined: Mar 22, 2005
Posts: 42929
    
  68
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.
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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...
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42929
    
  68
My first post in this thread mentioned the method that retrieves a Date object from a ResultSet. Is that what you're asking?
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

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...
Rajkumar balakrishnan
Ranch Hand

Joined: May 29, 2008
Posts: 445

Please somebody help me get rid of this problem.....
Niks capricorn
Greenhorn

Joined: Jun 05, 2009
Posts: 1
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...
 
jQuery in Action, 2nd edition
 
subject: Problem with date insertion in MySQL