This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with date insertion in MySQL

 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...

 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20955
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When i use the below code an IllegalArgumentException is thrown in the console...



 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 762
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Rajkumar balakrishnan
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ulf,
the da is the SQLDATE format and i use it to pass the date string into MySQL... Are you clear!!!
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please somebody help me get rid of this problem.....
 
Niks capricorn
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic