Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting the Date in to MySQL

 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 227
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 227
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1324
Android Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aravind Prasad great use of function str_to_date();
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic