0


I have issues with converting my string into a valid datetime2 for use in a MS SQL DB. I am using the CAST operator from SQL to convert it into a valid format. The Code looks like this:

include("sqlconnection.php");
$statement = $pdo->prepare("UPDATE auftrag SET bestelldatum = CAST(:bestelldatum AS datetime(7)), lieferdatum_wunsch = :lieferdatum, fk_id_kunde = (Select id_kunde from kunde where firmenname = :kunde), fk_id_auftragsstatus = (SELECT id_auftragsstatus from auftragsstatus WHERE bezeichnung = :status) WHERE id_auftrag = :id");
$result = $statement->execute(array("bestelldatum" => $_POST['bestelldatum'], "lieferdatum" => $_POST['lieferdatum'], "kunde" => $_POST['kunde'], "status" => $_POST['status'], "id" => $_POST['id']));
echo $result;

The format of the $_POST['bestelldatum'] looks like this: dd/mm/yyyy
I get the following error:

Uncaught PDOException: SQLSTATE[42000]: 
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]CAST oder CONVERT: Für 
den datetime-Typ wurden ungültige Attribute angegeben. in 
C:\xampp\htdocs\php\scripts\scripts.php:402 Stack trace: #0 
C:\xampp\htdocs\php\scripts\scripts.php(402): PDOStatement->execute(Array) #1 
C:\xampp\htdocs\php\scripts\scripts.php(30): saveAuftragChanges() #2 {main} 
thrown in C:\xampp\htdocs\php\scripts\scripts.php on line 402

Translated: cast or convert invalid attributes specified for type 'datetime'

I hope you can help me,
Sincerly,
Jan

  • Do you get an error if you convert `$_POST['bestelldatum']` to a valid date format in PHP and then remove the `CAST()` from your SQL? `$bestelldatum = date('Y-m-d',strtotime($_POST['bestelldatum']));`? – Mr Glass Jun 18 '18 at 11:02
  • I tried that, It gives the error: Error converting a String into a date or time. Its in this line: `$bestelldatum = date('Y-m-d',strtotime($_POST['bestelldatum']));`
    I tried, what `strtotime($_POST['bestelldatum'])` gives me, it returns a empty string     –  Jun 18 '18 at 11:06
  • Then `$_POST['bestelldatum']` may not be a proper data format. What do you get when you `var_dump($_POST['bestelldatum']);`? – Mr Glass Jun 18 '18 at 11:24
  • 1
    The dump gives the following: string(10) "27/06/2018" and dumping the $bestelldatum from the answer below gives the following: string(10) "2018-06-28" –  Jun 18 '18 at 11:31

1 Answers1

1

The strtotime function expects a date with slashes to be in US format (mm/dd/yyyy) but your date is in European format so it first needs to have the slashes changed to dashes. Use the following to format your date for SQL.

$bestelldatum = date('Y-m-d',strtotime(str_replace('/','-',$_POST['bestelldatum'])));

If a time is needed too try this instead (only change is to add a valid time to the variable created).

$bestelldatum = date('Y-m-d 00:00:01',strtotime(str_replace('/','-',$_POST['bestelldatum'])));
Dave
  • 5,108
  • 16
  • 30
  • 40
  • I get a propper format now: 2018-06-27, but still get the error: Error converting a String into a date or time. The SQL-Cast doesnt work either –  Jun 18 '18 at 11:29
  • For the cast issue remove the cast completely and just leave the placeholder `:bestelldatum` there. – Dave Jun 18 '18 at 11:33
  • Yeah I tried that, it gives the error: Error converting a String into a date or time. It looks like this now `UPDATE auftrag SET bestelldatum = :bestelldatum, lieferdatum_wunsch = :lieferdatum, fk_id_kunde = (Select id_kunde from kunde where firmenname = :kunde), fk_id_auftragsstatus = (SELECT id_auftragsstatus from auftragsstatus WHERE bezeichnung = :status) WHERE id_auftrag = :id` –  Jun 18 '18 at 11:33
  • And the execute looks like this: `execute(array("bestelldatum" => $bestelldatum ...));` –  Jun 18 '18 at 11:35
  • Not at all sure why it is complaining about the format. If it needs a time too see the modify to my answer above. I added a second version which includes a time. – Dave Jun 18 '18 at 11:41
  • Still the same error. The vardump `string(19) "2018-06-28 00:00:01"` –  Jun 18 '18 at 11:42
  • If I execute this statement `UPDATE auftrag SET bestelldatum = '2018-06-28 00:00:01' WHERE id_auftrag = 1;` In my SQL Management tool, it runs without a problem –  Jun 18 '18 at 11:46
  • A quick search shows that the format in my answer may not work in some cases but that YYYYMMDD should always work. So try `$bestelldatum = date('Ymd',strtotime(str_replace('/','-',$_POST['bestelldatum'])));` and see if that works. See [this answer](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) for other formats that may work. – Dave Jun 18 '18 at 11:46
  • Managed to get it fixed, I have to to the same for the lieferdatum now –  Jun 18 '18 at 11:47
  • And what did you do to fix it Jan? – Dave Jun 18 '18 at 11:48
  • I have deleted the `lieferdatum_wunsch = :lieferdatum` part of the SQL Statement as well as the array and it did work. Now I do the same thing for the lieferdatum, so: `$lieferdatum = date('Ymd', strtotime(str_replace('/', '-', $_POST['lieferdatum'])));` and fill in the SQL and Array part again. The deletion was a temporary test, to check if its this one, wich fails –  Jun 18 '18 at 11:52