to change the format of the date

[ajos]

Hi to all,
Ok the situation that i have is im adding things in my database(someth ing similar).The database that i was using was access and i had to change it to MySQL.As i came to know the date format of access and mysql are not the same i did something like this in my phpmyadmin(wamp )-->

SELECT DATE_FORMAT(now(), '%d/%m/%y')

when i execute the above lines in phpmyadmin i get something like this-->
[PHP]
DATE_FORMAT(now (), '%d/%m/%Y')
24/10/2007

[/PHP]

Im using jsp and servlets though.But still when i type the date in my jsp page i get an mysql exception-->

MYSQLException
SQLState:01004
Message:Data truncation: Incorrect date value: '24/10/2007' for column 'Req_Date' at row 1
Error:0

Ive no clue what this error states and how to solve this error.Can anybody help me in this regard.I hope ive conveyed my problem.
regards,
ajos

[r035198x]

Hi to all,
Ok the situation that i have is im adding things in my database(someth ing similar).The database that i was using was access and i had to change it to MySQL.As i came to know the date format of access and mysql are not the same i did something like this in my phpmyadmin(wamp )-->

SELECT DATE_FORMAT(now(), '%d/%m/%y')

when i execute the above lines in phpmyadmin i get something like this-->
[PHP]
DATE_FORMAT(now (), '%d/%m/%Y')
24/10/2007

[/PHP]

Im using jsp and servlets though.But still when i type the date in my jsp page i get an mysql exception-->


Ive no clue what this error states and how to solve this error.Can anybody help me in this regard.I hope ive conveyed my problem.
regards,
ajos

Post the code you used at the line that was reported for the exception.

[ajos]

Post the code you used at the line that was reported for the exception.

sorry i forgot the error message..here goes-->
MYSQLException
SQLState:01004
Message:Data truncation: Incorrect date value: '24/10/2007' for column 'Req_Date' at row 1
Error:0
this is all what im getting.

[r035198x]

sorry i forgot the error message..here goes-->
MYSQLException
SQLState:01004
Message:Data truncation: Incorrect date value: '24/10/2007' for column 'Req_Date' at row 1
Error:0
this is all what im getting.

I said post the Java code that is giving this error? You did say you are getting the error in the JSP right? Post also your column types.

[ajos]

I said post the Java code that is giving this error? You did say you are getting the error in the JSP right? Post also your column types.

Im sorry theres an error from my side...what i meant was after entering the date values in the form and then on submiting the error/exception was shown which i just mentioned before.i hope you got it.

[r035198x]

Im sorry theres an error from my side...what i meant was after entering the date values in the form and then on submiting the error/exception was shown which i just mentioned before.i hope you got it.

What date value did you type in?
After you submit the form, is there not a servlet that updates the database wth those entered values?

[ajos]

What date value did you type in?
After you submit the form, is there not a servlet that updates the database wth those entered values?

What i typed is something like this 24/10/2007.Yes there is a class which takes care of the database i.e what the servlet does is simply execute the query and forward it to a jsp page thats it.So where possibly have i gone wrong here?

[r035198x]

What i typed is something like this 24/10/2007.Yes there is a class which takes care of the database i.e what the servlet does is simply execute the query and forward it to a jsp page thats it.So where possibly have i gone wrong here?

What type is the column Req_Date ?
So there is some Java code that is run to insert the values. That's the code I've been asking you to post.

[ajos]

What type is the column Req_Date ?
So there is some Java code that is run to insert the values. That's the code I've been asking you to post.

after making some changes to the type ive changed it to date.it was datetime earlier.ok here goes my code--->
Im sure it has nothing to do wwith the code here(I would like to believe it this way).

String s7 = httpservletrequest.getParameter("date");
        String s8 = httpservletrequest.getParameter("branch");
        String s9 = httpservletrequest.getParameter("publicity_nature");
        s3 = httpservletrequest.getParameter("brief1");
        s4 = httpservletrequest.getParameter("brief2");
        s5 = httpservletrequest.getParameter("brief3");
        s6 = httpservletrequest.getParameter("brief4");
        String s10 = httpservletrequest.getParameter("specifics1");
        String s11 = httpservletrequest.getParameter("specifics2");
        String s12 = httpservletrequest.getParameter("specifics3");
        String s13 = httpservletrequest.getParameter("specifics4");
        String s14 = httpservletrequest.getParameter("medium");
        String s15 = httpservletrequest.getParameter("period");
        String s16 = httpservletrequest.getParameter("market");
        String s17 = httpservletrequest.getParameter("proposed");
        String s18 = httpservletrequest.getParameter("remarks");
        if(s4.length() == 0)
            s4 = " ";
        if(s5.length() == 0)
            s5 = " ";
        if(s6.length() == 0)
            s6 = " ";
        if(s11.length() == 0)
            s11 = " ";
        if(s12.length() == 0)
            s12 = " ";
        if(s13.length() == 0)
            s13 = " ";
        String s = "";
        String s1 = "";
        
try{
con=db.getConnection();
        	int i = 0;
            statement = con.createStatement();
            String s2 = "SELECT Internal_Pub_Counter FROM Internal_Counter";
            for(resultset = statement.executeQuery(s2); resultset.next();)
            i = resultset.getInt("Internal_Pub_Counter");
            int j = i + 1;
            con.setTransactionIsolation(2);
            con.setAutoCommit(false);
            s2 = "UPDATE Internal_Counter SET Internal_Pub_Counter =" + j;
            statement.executeUpdate(s2);
            String s19 = "('" + s8 + "'" + ",'" + s9 + "'" + ",'" + s3 + "'" + ",'" + s4 + "'" + ",'" + s5 + "'" + ",'" + s6 + "'" + ",'" + s10 + "'" + ",'" + s11 + "'" + ",'" + s12 + "'" + ",'" + s13 + "'" + ",'" + s14 + "'" + ",'" + s15 + "'" + ",'" + s16 + "'" + ",'" + s17 + "'" + ",'" + s7 + "'" + ",'" + s18 + "'," + i + ")";
            String s20 = "INSERT INTO Internal_Publicity_Details (Branch,Publicity_Nature,Brief_Part1,Brief_Part2,Brief_Part3,Brief_Part4,Specifics_1,Specifics_2,Specifics_3,Specifics_4,Publicity_Media,Publicity_Period,Market_Coverage,Proposed_By,Req_Date,Remarks,Id_No) VALUES ";
            s2 = s20 + s19;
            statement.executeUpdate(s2);
            con.commit();
            con.setTransactionIsolation(2);....

[r035198x]

You should have posted only the part that is throwing the exception.
So which part (line number) is reported for the exception? I hope you are changing the string (representation of the date) to Date type before inserting the date into the database.

[ajos]

You should have posted only the part that is throwing the exception.
So which part (line number) is reported for the exception?

I hope you are changing the string (representation of the date) to Date type before inserting the date into the database

.

can you please elaborate on this line.how shud i do that and where?
and as for the exception goes the exception is shown on my console ie.this-->
MYSQLException
SQLState:01004
Message:Data truncation: Incorrect datetime value: '24/10/2007' for column 'Req_Date' at row 1
Error:0
nothing more in the tomcat stacktrace.Can somebody please help me

[r035198x]

If you have a date column in your database then the you are expected to set dates only in that column. Similarly for other column types.
If the user enters the date as a String, then you need to change it to a Date before entering it into the database. You can do this using Java code by changing the string to a java.sql.Date or in the sql by using a TO_DATE function if your database allows it.

P.S You should take time to read Java coding conventions.
P.P.S You should take time to read about PreparedStateme nts.

[ajos]

If you have a date column in your database then the you are expected to set dates only in that column. Similarly for other column types.
If the user enters the date as a String, then you need to change it to a Date before entering it into the database. You can do this using Java code by changing the string to a java.sql.Date or in the sql by using a TO_DATE function if your database allows it.

P.S You should take time to read Java coding conventions.
P.P.S You should take time to read about PreparedStateme nts.

hi r035198x,
After some research ive found that whatever date that i will enter in my db,mysql stores them in the format yyyy-mm-dd.Thats not an issue for me.Also i found out that it would be a good idea to make the changes in my query.My query is-->
[HTML]INSERT INTO Internal_Public ity_Details (Branch,Publici ty_Nature,Brief _Part1,Brief_Pa rt2,Brief_Part3 ,Brief_Part4,Sp ecifics_1,Speci fics_2,Specific s_3,Specifics_4 ,Publicity_Medi a,Publicity_Per iod,Market_Cove rage,Proposed_B y,Req_Date,Remarks,Id_No) VALUES [/HTML]
where Req_Date is my date column.I tried to do something like this--->
[HTML]INSERT INTO Internal_Public ity_Details (Branch,Publici ty_Nature,Brief _Part1,Brief_Pa rt2,Brief_Part3 ,Brief_Part4,Sp ecifics_1,Speci fics_2,Specific s_3,Specifics_4 ,Publicity_Medi a,Publicity_Per iod,Market_Cove rage,Proposed_B y,Req_Date,Rema rks,Id_No) VALUES (str_to_date('0 1/02/2007','%d/%m/%Y')[/HTML] gave me syntax error.
even though i dont really know about it clearly can you suggest something which possiblymay work?
regards.
ajos

[r035198x]

hi r035198x,
After some research ive found that whatever date that i will enter in my db,mysql stores them in the format yyyy-mm-dd.Thats not an issue for me.Also i found out that it would be a good idea to make the changes in my query.My query is-->
[HTML]INSERT INTO Internal_Public ity_Details (Branch,Publici ty_Nature,Brief _Part1,Brief_Pa rt2,Brief_Part3 ,Brief_Part4,Sp ecifics_1,Speci fics_2,Specific s_3,Specifics_4 ,Publicity_Medi a,Publicity_Per iod,Market_Cove rage,Proposed_B y,Req_Date,Rema rks,Id_No) VALUES [/HTML]
where Req_Date is my date column.I tried to do something like this--->
[HTML]INSERT INTO Internal_Public ity_Details (Branch,Publici ty_Nature,Brief _Part1,Brief_Pa rt2,Brief_Part3 ,Brief_Part4,Sp ecifics_1,Speci fics_2,Specific s_3,Specifics_4 ,Publicity_Medi a,Publicity_Per iod,Market_Cove rage,Proposed_B y,Req_Date,Rema rks,Id_No) VALUES (str_to_date('0 1/02/2007','%d/%m/%Y')[/HTML] gave me syntax error.
even though i dont really know about it clearly can you suggest something which possiblymay work?
regards.
ajos

What was the full sql that you had used when you got the syntax error?

[ajos]

What was the full sql that you had used when you got the syntax error?

INSERT INTO Internal_Public ity_Details (Branch,Publici ty_Nature,Brief _Part1,Brief_Pa rt2,B rief_Part3,Brie f_Part4,Specifi cs_1,Specifics_ 2,Spe cifics_3,Specif ics_4,Publicity _Media,Publicit y_Per iod,Market_Cove rage,Proposed_B y,Req_Date,Rema rks,I d_No) VALUES (str_to_date('0 1/02/2007','%d/%m/%Y')

and i dont really know what str_to_date does,im guessing it converts the string into the date, and also what is this for '01/02/2007'.Im sorry for asking you such questions ive no idea about database.
regards,
ajos