• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with date insertion in MySQL

 
Ranch Hand
Posts: 445
Android Eclipse IDE Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...

 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When i use the below code an IllegalArgumentException is thrown in the console...



 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please somebody help me get rid of this problem.....
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
reply
    Bookmark Topic Watch Topic
  • New Topic