2

I am trying to insert 2 dates through a form in my database, date format in the form is a string like this 01/01/2020 and the field in the database is DATE so I understand it will take YYYY/MM/DD right?

I have trid this query :

INSERT INTO property_tenant (id_user, id_property, check_in, check_out) 
VALUES(1375965, 119, STR_TO_DATE("01/01/2020", '%d/%m/%y'), 
                     STR_TO_DATE("01/01/2021", '%d/%m/%y'));

I have also tried this : 

DATE_FORMAT(STR_TO_DATE("01/01/2021", '%d/%m/%y'), '%Y-%m-%d')

but I keep getting this error:

code: 'ER_TRUNCATED_WRONG_VALUE', errno: 1292, sqlMessage: "Truncated incorrect date value: '01/01/2020'"

Barbaros Özhan
  • 39,060
  • 9
  • 21
  • 45
jesus fernandez
  • 305
  • 1
  • 9
  • Date's the database ***should*** be stored as YYYYMMDD (`DATE`, `DATETIME` etc.) date format, which is neither US or European style but "Database Style"!! – Martin Feb 08 '20 at 13:10

1 Answers1

2

you can directly use STR_TO_DATE() function as

STR_TO_DATE('1,1,2021','%d,%m,%Y')

which already yields a date result. Indeed a date column doesn't have a format within a table. fmt part( '%d,%m,%Y' ) is just needed to express pieces of the literal conforming with the time portions(day,month,Year) to be ordered correctly.

Demo

Barbaros Özhan
  • 39,060
  • 9
  • 21
  • 45
  • if the dates entered in the form come separates by / should not I set it like this? ```STR_TO_DATE('1/1/2021','%d/%m/%Y')``` – jesus fernandez Feb 08 '20 at 12:47
  • dunno why it is not working I have updated the query above as it is now but I get this output: ``` INSERT INTO property_tenant (id_user, id_property, check_in, check_out) VALUES (1375965, 119, STR_TO_DATE("01/01/2020", '%d/%m/%y'), STR_TO_DATE("01/01/2021", '%d/%m/%y')) Error Code: 1292. Truncated incorrect date value: '01/01/2020' 0.000 sec ``` – jesus fernandez Feb 08 '20 at 12:50
  • Have a look at [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=92c618ef7a3eb6a23f612d8145c6cc1b) with capital Y @jesusfernandez – Barbaros Özhan Feb 08 '20 at 12:53
  • I must be dumb but it looks exactly the same to what I am doing... I have trid with dd/mm/yyyy in the format also D/M/Y and all different combinations I do not see where is the mistake... – jesus fernandez Feb 08 '20 at 12:57
  • :) pay attention to the capital `Y`(consider the demo) @jesusfernandez – Barbaros Özhan Feb 08 '20 at 12:59
  • 1
    OMG I thought if I set Y all the other fileds such as day and month shuld be capitals as well, OK sorted now!!! thanks a lot – jesus fernandez Feb 08 '20 at 13:03