• 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

Inserting the Date in to MySQL

 
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 227
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1325
Android Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Aravind Prasad great use of function str_to_date();
 
reply
    Bookmark Topic Watch Topic
  • New Topic