CODEWITHSUNDEEP
phpsql-serversqlsrv
daniellandete
daniellandete

Reputation: 121

what is the date format to insert a date using sqlsrv_query?

I get all rows by this sql query:

$sql = "SELECT * FROM USERS";
$stmt3 = sqlsrv_query( $conn, $sql3 , $params, $options );

But when I want to insert:

$sql2 = 
   "INSERT INTO USER (USER_ID, USER_NOM, USER_DATE) 
   VALUES (".$row['USER_ID'].", '".$row['USER_NOM']."','".$row['USER_DATE']->format('Y-m-d h:i:s.v')."')";

I get this error:

Converting the varchar data type to datetime produced an out of range value.

This is my $sql2:

INSERT INTO USER (USER_ID, USER_NOM, USER_DATE) VALUES (1,DANI,'2020-07-22 12:00:00.000')

Upvotes: 0

Views: 616

Answers (2)

Zhorov
Zhorov

Reputation: 29943

You need to consider the following:

  • You need to use parameterized statements to prevent possible SQL injection issues. As is mentioned in the documentation, the sqlsrv_query() function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply. This function provides a streamlined method to execute a query with a minimum amount of code. The sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.
  • By default smalldatetime, datetime, date, time, datetime2, and datetimeoffset types will be returned as PHP DateTime objects, but this behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level (more explanations can be found here).

You have two options, if you are sure, that date/time values are returned as PHP DateTime objects:

  • Pass datetime values as text using unambiguous format (e.g. 2020-07-22T12:00:00.000)
  • Pass datetime values as PHP DateTime objects using extended syntax for parameters for the sqlsrv_query() call.

Example (datetime values are passed as text):

<?php
// SELECT statement
$sql3  = "SELECT * FROM USERS";
$stmt3 = sqlsrv_query($conn, $sql3 , $params, $options);
if ($stmt3 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}   

// INSERT statements
while ($row = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) { 
    $sql2 = "
        INSERT INTO USER (USER_ID, USER_NOM, USER_DATE) 
        VALUES (?, ?, ?)
    ";
    $params = array(
       $row['USER_ID'], 
       $row['USER_NOM'], 
       $row['USER_DATE']->format('Y-m-d\TH:i:s.v'));
    );
    $stmt2 = sqlsrv_query($conn, $sql2 , $params);

}
?>

Example (datetime values are passed as PHP DateTime objects):

<?php
// SELECT statement
$sql3  = "SELECT * FROM USERS";
$stmt3 = sqlsrv_query($conn, $sql3 , $params, $options);
if ($stmt3 === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}   

// INSERT statements
while ($row = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) { 
    $sql2 = "
        INSERT INTO USER (USER_ID, USER_NOM, USER_DATE) 
        VALUES (?, ?, ?)
    ";
    $params = array(
        array($row['USER_ID'], SQLSRV_PARAM_IN),
        array($row['USER_NOM'], SQLSRV_PARAM_IN),
        array($row['USER_DATE'], SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_DATETIME, SQLSRV_SQLTYPE_DATETIME)
    );
    $stmt2 = sqlsrv_query($conn, $sql2 , $params);

}
?>

Upvotes: 3

KUMAR
KUMAR

Reputation: 1995

The Datetimevalue to be inserted in Datetime type , it should be in 'YYYY-MM-DD HH:MM:SS' format.

The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

Note:- For More Details Please refer this https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Upvotes: 0

Related Questions