Updating table with form using PHP to MS SQL

Question

I am struggling trying to update a row in an Azure SQL database. What I am trying to do is to update a row with some input variables along with a fresh datestamp. If I input the following (for test purposes), my database is updated, but the date is way off:

"UPDATE TABLENAME set COL1 = ".$_POST[VAL1].", COL2 = ".$_POST[VAL2].", COL3 = 2020-03-20 WHERE COL0 = 'VAL0'"

giving me a datestamp looking like this: 1905-06-21T00:00:00.0000000 I have been trying just around a hundred ways of formatting the date() variable, putting it in my SQL statement like this:

"UPDATE TABLENAME set COL1 = ".$_POST[VAL1].", COL2 = ".$_POST[VAL2].", COL3 = ".date()." WHERE COL0 = 'VAL0'"

Needless to say, COL3 is my datestamp column. But I cannot get the database to accept my datestamp formatting. I have tried YYYY-mm-dd xyz1234 in countless variants inside date(), but to no avail. The database has the following collation set: SQL_Latin1_General_CP1_CI_AS. Any pointers?

Answer 1

First, about your incorrect date value. It is correct, because when you miss '' around 2020-03-20, SQL Server makes implicit data type conversion. Next example demonstrates this:

Implicit conversion:

DECLARE @date datetime

SELECT @date = 2020-03-20
SELECT @date

SELECT DATEADD(day, 2020-03-20, 0)

Output:

1905-06-21 00:00:00.000

Second, if you want to pass date and time values, just use appropriate format - 'yyyy-MM-dd', 'yyyyMMdd hh:nn:ss' or 'yyyy-MM-ddThh:nn:ss':

"UPDATE TABLENAME set COL1 = ".$_POST[VAL1].", COL2 = ".$_POST[VAL2].", COL3 = '2020-03-20' WHERE COL0 = 'VAL0'"

I don't know how you make your connection to SQL Server, but try to use prepared statements.

Update (Retrieve and send date and time values from and to SQL Server):

Based on driver that you use to connect to SQL Server, you may retrieve date and time values as text or as PHP datetime object (if you use PHP Driver for SQL Server), but you need to send these values as text. In your case values are returned as text. So you need to generate universal datetime value (in 'yyyy-MM-ddThh:nn:ss' for example) as text.

Next example shows some valid and invalid combinations for UPDATE T-SQL statement for your datetime column. It is tested with PHP Driver for SQL Server 4.0.3.

<?php
# Value from database as text
$row['COL3'] = '2019-03-29T11:35:30.0000000';

# Valid statement
$tsql = "UPDATE TABLENAME SET COL3 = '".substr($row['COL3'], 0, 19)."' ";

# Valid statement - date("Y-m-d\Th:i:s") will return current datetime
$tsql = "UPDATE TABLENAME SET COL3 = '".date("Y-m-d\Th:i:s")."' ";

# Invalid statement - date("d-m-Y h:i:s", $row['COL3']) expects int as second parameter, 
# generates warning and returns '01-01-1970 12:33:39' as result  
$tsql = "UPDATE TABLENAME SET COL3 = '".date("d-m-Y h:i:s", $row['COL3'])."' ";
?>