0

I want to add date from my JSP to mysql database using Servlet, if i write a query then STR_TO_DATE work properly for insertion, but if i use STR_TO_DATE in Stored Procedure it gives me "com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '11-10-2015' for column 'DATE' at row 1".

My Query is:

INSERT INTO TABLENAME (DATE) VALUES (STR_TO_DATE('01/01/2010','%d-%m-%Y'));"

Any Idea?

tadman
  • 208,517
  • 23
  • 234
  • 262
Abrar Ansari
  • 127
  • 8
  • i know but my question is why the function STR_TO_DATE is working in normal query and not in a stored Procedure???? – Abrar Ansari Oct 16 '17 at 20:34
  • One question mark is enough. There's no need to get crazy??????? – tadman Oct 16 '17 at 20:35
  • `SELECT STR_TO_DATE('01/01/2010','%d-%m-%Y');` returns `(null)` `SELECT STR_TO_DATE('01/01/2010','%d/%m/%Y');` returns `2010-01-01` your formating within STR_TO_DATE is wrong – Raymond Nijland Oct 16 '17 at 20:35

2 Answers2

2

The second parameter within the function STR_TO_DATE is using the wrong format. You need to use %d/%m/%Y instead of %d-%m-%Ybecause your date is formatted with / seperator.

INSERT INTO TABLENAME (DATE) VALUES (STR_TO_DATE('01/01/2010','%d/%m/%Y'));
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
0

STR_TO_DATE requires the formatting of the strings to match:

INSERT INTO TABLENAME (DATE) VALUES (STR_TO_DATE('01/01/2010','%d/%m/%Y'));

Refer to the docs for full details.

wogsland
  • 9,106
  • 19
  • 57
  • 93
  • Don't forget to accept the answer if it solved your problem ;) – wogsland Oct 16 '17 at 20:48
  • 1
    Your answer is leading to a miss information about the use of a function in an insert command. It is perfect fine to use a function inside the values parameters. look it working here: http://sqlfiddle.com/#!9/fd0d19/1 – Jorge Campos Oct 16 '17 at 21:49
  • Please edit it removing that information so I can retract the downvote. – Jorge Campos Oct 17 '17 at 11:52