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

Inserting the Date in to MySQL

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Dear Ranchers,
I'm using JSP and MySQL in my project. When i tried to insert the date into the table it is always showing error. I was actully converting from Oracle to MySQL.
In oracle i'm using

The way i was inserting is


I want to insert into the table as dd-mm-yyyy format..

Please help me ranchers..

Thanks in advance

regards

Aravind Prasad
Aditya Jha
Ranch Hand

Joined: Aug 25, 2003
Posts: 227

First things first: In DB, if the column type is DATE, it doesn't store the date values in a particular 'format' (dd-MMM-yyyy or whatever). What it stores is some kind of a number or expression which helps it to uniquely indicate a date value. So, your statement

I want to insert into the table as dd-mm-yyyy format..

is not valid. If the column type is DATE (which I assume as you were inserting a value using 'to_date') then it will hold date-value and not string (formatted).

What I can realize from your post is that while getting the value back, you may want to display (or parse) it as dd-MMM-yyyy. However, for this you have to SELECT the value using 'to_char'. The value in DB is of type DATE and can be retrieved in ANY format. So, something like

should work.
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Dear Aditya ,
Thanks for your reply. What my problem is
I've the code for inserting a date column in Oracle table

for egs :
String d = "10-11-2006"
insert into aa values (to_char('dd-mm-yyyy',d);

This is working perfectly in Oracle 8i.

Now my client want to host the site.. and he wants to conver the oracle to MySQL for hosting. So in a particular point there is no to_char function in MySQL. So i could't inser the date into the MySQL table..


My Doubt : How to insert a date column into MySQL table ?


Please help me

Regards

Aravind Prasad
Aditya Jha
Ranch Hand

Joined: Aug 25, 2003
Posts: 227

Still, my favourite (and DB-neutral) way would be


As you see, if you work with correct SQL data-types, most of the time you don't have to worry about the exact DB application used.

Hope this helps!
[ December 13, 2006: Message edited by: Aditya N Jha ]
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Thanks Aditya
I hope my problem is not solved yet. Let me send the table details to you.


But the query will execute when I insert the date directly like this





But when I do a select query, I'm getting 00000000




How can i solve this..

Please help me ranchers!!

regards
Aravind Prasad
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321

Originally posted by Aravind Prasad:
I want to insert into the table as dd-mm-yyyy format..


Well regarding your dateformat requirement you have to follow the SimpleDateFormat class its helps you to apply the format whatever you want. What you need to do to create an object of simpleDateFormat class by defining a format into the constructor in string.

Your code would be like this.. what Aditya refer you above.. try this..



still facing problem... then share with us.


Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Dear Ranchers,
Finally i got the answer. I want to insert the date in dd-mm-yyyy in the MySQL table

The code is this



Thank you for your help

regards

Aravind Prasad
Neerav Narielwala
Ranch Hand

Joined: Dec 08, 2006
Posts: 106
Aravind Prasad great use of function str_to_date();


<a href="http://www.java-tips.org/java-tutorials/tutorials/" target="_blank" rel="nofollow">Java Tutorials</a> | <a href="http://www.planet-java.org" target="_blank" rel="nofollow">Java Weblog</a> | <a href="http://computer-engineering.science-tips.org" target="_blank" rel="nofollow">Computing Articles</a>
 
jQuery in Action, 2nd edition
 
subject: Inserting the Date in to MySQL