Can't add row in sql server because of date

Question

I have a web application where I need to insert data in my SQL Server table, the problem is that I have this error in my Apache log:

General error: 241 Conversion failed when converting date and/or time from character string.

I have the same error when I try directly inside ms sql server. There is my php code :

public function addVisiteClient(Request $request){
    require __DIR__.DIRECTORY_SEPARATOR."..".DIRECTORY_SEPARATOR."Entity".DIRECTORY_SEPARATOR."sqlconnect.php";
      $idCli = $request->request->has("id")?$request->request->get("id"):"";
      $cadre = $request->request->has("cadre")?$request->request->get("cadre"):"";
      $type = $request->request->has("type")?$request->request->get("type"):"";
      $duree = $request->request->has("duree")?$request->request->get("duree"):"";
      $obs = $request->request->has("observation")?$request->request->get("observation"):"";
      $dateRelance = $request->request->has("dateRelance")?$request->request->has("dateRelance"):"";
      $insert = "INSERT INTO VISITE (CLCLEUNIK, DATERELANCE, DATEVIS, CADREVISITE, TYPE, DUREE, OBERSVAT) VALUES (:idCli, CONVERT(DATETIME, :dateRelance), GETDATE(), :cadre, :type, :duree, :observation)";
      $prepare=$pdo2->prepare($insert);
      $prepare->execute(array(
        ":idCli" => $idCli,
        ":dateRelance" => $dateRelance,
        ":cadre" => $cadre,
        ":type" => $type,
        ":duree" => $duree,
        ":observation" => $obs
      ));
      $prepare->closeCursor();
      return new Response("Ajout effectué");

     }

And there is an example of data :

INSERT INTO VISITE (CLCLEUNIK, DATERELANCE, DATEVIS, CADREVISITE, TYPE, DUREE, OBERSVAT) VALUES (4080508, CONVERT(DATETIME, '2020-02-27 12:00:00'), GETDATE(), 1, 1, 10, 'test')

Answer 1

You must use a string-based date format, you should pick one that is safe and works in every SQL Server instance, regardless of date format, language and regional settings.

--Format: mon dd yyyy hh:mmAM (or PM)
--result: Dec 7 2018 4:09PM
SELECT convert(varchar, getdate(), 100)  

--Format: mm/dd/yyyy
--result: 12/07/2018
SELECT convert(varchar, getdate(), 101)

--Format: yyyy.mm.dd
--result: 2018.12.07
SELECT convert(varchar, getdate(), 102)

--Format: dd/mm/yyyy
--result: 07/12/2018
SELECT convert(varchar, getdate(), 103)

--Format: dd.mm.yyyy
--result: 07.12.2018
SELECT convert(varchar, getdate(), 104) 

--Format: dd-mm-yyyy
--result: 07-12-2018
SELECT convert(varchar, getdate(), 105)

--Format: dd mon yyyy
--result: 07 Dec 2018
SELECT convert(varchar, getdate(), 106)

--Format: mon dd, yyyy
--result: Dec 07, 2018
SELECT convert(varchar, getdate(), 107)

--Format: hh:mm:ss
--result: 15:49:24
SELECT convert(varchar, getdate(), 108)

--Format: mon dd yyyy hh:mm:ss:mmmAM (or PM)
--result: Dec 7 2018 3:50:13:540PM
SELECT convert(varchar, getdate(), 109) 

--Format: mm-dd-yyyy
--result: 12-07-2018
SELECT convert(varchar, getdate(), 110)

--Format: yyyy/mm/dd -- yyyymmdd - ISO date format - international standard - works with any language setting
--result: 2018/12/07
SELECT convert(varchar, getdate(), 111)

--Format: yyyymmdd
--result: 20181207
SELECT convert(varchar, getdate(), 112)

--Format: dd mon yyyy hh:mm:ss:mmm
--result: 07 Dec 2018 15:53:14:053
SELECT convert(varchar, getdate(), 113)

--Format: hh:mm:ss:mmm(24h)
--result: 15:54:05:693
SELECT convert(varchar, getdate(), 114)

--Format: yyyy-mm-dd hh:mm:ss(24h)
--result: 2018-12-07 15:54:23
SELECT convert(varchar, getdate(), 120)

--Format: yyyy-mm-dd hh:mm:ss.mmm
--result: 2018-12-07 15:55:15.630
SELECT convert(varchar, getdate(), 121)

--Format: yyyy-mm-ddThh:mm:ss.mmm
--result: 2018-12-07T15:55:44.147
SELECT convert(varchar, getdate(), 126)

--Without century (YY) date / datetime conversion - there are exceptions!

--Format: mon dd yyyy hh:mmAM (or PM)
--result: Dec 7 2018 3:56PM
SELECT convert(varchar, getdate(), 0)

--Format: mm/dd/yy
--result: 12/07/18
SELECT convert(varchar, getdate(), 1)

--Format: yy.mm.dd
--result: 18.12.07
SELECT convert(varchar, getdate(), 2)

--Format: dd/mm/yy
--result: 07/12/18
SELECT convert(varchar, getdate(), 3)

--Format: dd.mm.yy
--result: 07.12.18
SELECT convert(varchar, getdate(), 4)  

--Format: dd-mm-yy
--result: 07-12-18
SELECT convert(varchar, getdate(), 5)

--Format: dd mon yy
--result: 07 Dec 18
SELECT convert(varchar, getdate(), 6)  

--Format: mon dd, yy
--result: Dec 07, 18
SELECT convert(varchar, getdate(), 7)

--Format: hh:mm:ss
--result: 16:02:32
SELECT convert(varchar, getdate(), 8)

--Format: mon dd yyyy hh:mm:ss:mmmAM (or PM)
--result: Dec 7 2018 4:03:02:100PM
SELECT convert(varchar, getdate(), 9)

--Format: mm-dd-yy
--result: 12-07-18
SELECT convert(varchar, getdate(), 10)

--Format: yy/mm/dd
--result: 18/12/07
SELECT convert(varchar, getdate(), 11) 

--Format: yymmdd
--result: 181207
SELECT convert(varchar, getdate(), 12)

--Format: dd mon yyyy hh:mm:ss:mmm
--result: 07 Dec 2018 16:05:07:547
SELECT convert(varchar, getdate(), 13)

--Format: hh:mm:ss:mmm(24h)
--result: 16:05:34:363
SELECT convert(varchar, getdate(), 14)

--Format: yyyy-mm-dd hh:mm:ss(24h)
--result: 2018-12-07 16:06:14
SELECT convert(varchar, getdate(), 20) 

--Format: yyyy-mm-dd hh:mm:ss.mmm
--result: 2018-12-07 16:06:43.970
SELECT convert(varchar, getdate(), 21)

--Format: mm/dd/yy hh:mm:ss AM (or PM)
--result: 12/07/18 4:06:59 PM
SELECT convert(varchar, getdate(), 22)

--Format: yyyy-mm-dd
--result: 2018-12-07
SELECT convert(varchar, getdate(), 23)

--Format: hh:mm:ss
--result: 16:08:11
SELECT convert(varchar, getdate(), 24)

--Format: yyyy-mm-dd hh:mm:ss.mmm
--result: 2018-12-07 16:08:28.353
SELECT convert(varchar, getdate(), 25)

Convert String to Datetime

Convert Datetime to Date

AND here

SEE THIS ANSWER : Insert converted varchar into datetime sql

Answer 2

Use format('YYYY-m-d H:i:s') to format the date parameter

$prepare->execute(array(
        ":idCli" => $idCli,
        ":dateRelance" => $dateRelance->format('YYYY-m-d H:i:s'),
        ":cadre" => $cadre,
        ":type" => $type,
        ":duree" => $duree,
        ":observation" => $obs
      ));