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

Improve this question
4
  • 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']));? Commented Jun 18, 2018 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'])); <br> I tried, what strtotime($_POST['bestelldatum']) gives me, it returns a empty string Commented Jun 18, 2018 at 11:06
  • Then $_POST['bestelldatum'] may not be a proper data format. What do you get when you var_dump($_POST['bestelldatum']);? Commented Jun 18, 2018 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" Commented Jun 18, 2018 at 11:31

1 Answer 1

Reset to default
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'])));
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

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
For the cast issue remove the cast completely and just leave the placeholder :bestelldatum there.
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
And the execute looks like this: execute(array("bestelldatum" => $bestelldatum ...));
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.