Inserting the Date in to MySQL

[Aravind Prasad]

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

insert into aa values (to_date('dd-mm-yyyy','10-12-2006'))

The way i was inserting is

insert into aa values(10-12-2006);

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

Please help me ranchers..

Thanks in advance

regards

Aravind Prasad

[Aditya Jha]

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

SELECT TO_CHAR(MY_DATE_COLUMN, 'DD-MMM-YYYY') FROM AA

should work.

[Aravind Prasad]

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]

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

myPrepStmt = con.prepareStatement("INSERT INTO AA VALUES (?)"); 
// Not this
// myPrepStmt = con.prepareStatement("INSERT INTO AA VALUES (TO_CHAR(?, 'DD-MMM-YYYY'))");
 
String d = "10-11-2006";
java.util.Date date = new SimpleDateFormat("dd-MMM-yyyy").parse(d);
java.sql.Date sqlDate = new java.sql.Date(date.getTime());
myPrepStmt.setDate(1, sqlDate);
 
// Not this
// myPrepStmt.setString(1, d);
 

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]

Thanks Aditya
I hope my problem is not solved yet. Let me send the table details to you.

mysql> desc aa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| t     | Datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
mysql> insert into aa values (to_char('10/11/2006','dd/mm/yyyy'));
ERROR 1305 (42000): FUNCTION test.to_char does not exist

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

mysql> insert into aa values (10/11/2006);
Query OK, 1 row affected (0.02 sec)

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

mysql> select * from aa;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
 
mysql>

How can i solve this..

Please help me ranchers!!

regards
Aravind Prasad

[Muhammad Saifuddin]

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..

myPrepStmt = con.prepareStatement("INSERT INTO AA VALUES (?)"); 
 
String d = "10-11-2006";
java.util.Date date = new SimpleDateFormat("dd-mm-yyyy").parse(d);
java.sql.Date sqlDate = new java.sql.Date(date.getTime());
myPrepStmt.setDate(1, sqlDate);

still facing problem... then share with us.

[Aravind Prasad]

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

    myPrepStmt = con.prepareStatement("INSERT INTO AA VALUES [B](str_to_date(?,'%d-%m-%Y')[/B]"); 
     
    String d = "10-11-2006";
    java.util.Date date = new SimpleDateFormat("dd-mm-yyyy").parse(d);
    java.sql.Date sqlDate = new java.sql.Date(date.getTime());
    myPrepStmt.setDate(1, sqlDate);

Thank you for your help

regards

Aravind Prasad

[Neerav Narielwala]

Aravind Prasad great use of function str_to_date();