Prepared Statement with TS

I’m having trouble with this syntax for sqlsrv driver.

This works just fine:

  • Possible to set SQL Server Remote Query Timeout per Query for Linked Server calls?
  • SQL: How to make table name in stored procedure dynamic
  • Select rows where price didn't change
  • Timeout issue in Backup-SQLDatabase cmdlet
  • Entity Framework: SqlGeography vs DbGeography
  • Delete where both columns match
  •   $sql = "SELECT * from Table1 WHERE (Table1.Time >= {ts '2017-05-08
              00:00:00' } AND Table1.Time < {ts '2017-05-10 00:00:00' })";
      $stmt = sqlsrv_query( $conn, $sql );
    
      $tbl = "<table>"
      while ($row = sqlsrv_fetch_array($stmt)){
          $tbl .= "<tr><td>".$row[0]."</td><td>".$row[1]->format('Y-m-d H:i:s')."</td><td align=\"center\">".$row[2]."</td></tr>";
      }
      $tbl .= "</table>";
      echo $tbl;
    

    But this doesn’t work out all:

          $sql = "SELECT * from Table1 WHERE (Table1.Time >= {ts ? } AND Table1.Time < {ts ? })";
          $stmt = sqlsrv_prepare( $conn, $sql, array('2017-05-08 00:00:00','2017-05-10 00:00:00'));
          $result = sqlsrv_execute($stmt);
          $tbl = "<table>"
          while ($row = sqlsrv_fetch_array($stmt)){
              $tbl .= "<tr><td>".$row[0]."</td><td>".$row[1]->format('Y-m-d H:i:s')."</td><td align=\"center\">".$row[2]."</td></tr>";
          }
          $tbl .= "</table>";
          echo $tbl;
    

    I get this error:

    An invalid parameter was passed to sqlsrv_execute. [message] => An invalid parameter was passed to sqlsrv_execute. )
    

    Is there a proper way to pass date variables to the { ts ? } statement?

  • SQL Group BY, Top N Items for each Group
  • SSIS - Derived Column
  • Correct use of SCOPE_IDENTITY function within simple stored procedure
  • Bit-flipping operations in T-SQL
  • Are temporary tables thread-safe?
  • T-SQL query update null values
  • One Solution collect form web for “Prepared Statement with TS”

    You are unlikely to get prepared statements working with ODBC escape sequences. Unlike MySQL (which has a UNIX_TIMESTAMP function) there are no functions built in to SQL Server that will convert to a Unix timestamp for you. You could do the math yourself, but maybe it’s just easier to do it in PHP:

    $begin = strtotime('2017-05-08 00:00:00');
    $end = strtotime('2017-05-10 00:00:00');
    $sql = "SELECT * FROM Table1 WHERE Table1.Time >= ? AND Table1.Time < ?)";
    $stmt = sqlsrv_prepare($conn, $sql, array($begin, $end));
    $result = sqlsrv_execute($stmt);
    $tbl = "<table>"
    while ($row = sqlsrv_fetch_array($stmt)){
        $row[1] = $row[1]->format('Y-m-d H:i:s');
        $tbl .= "<tr><td>$row[0]</td><td>$row[1]</td><td align=\"center\">$row[2]</td></tr>";
    }
    $tbl .= "</table>";
    echo $tbl;
    

    You should enumerate the columns that you’re fetching instead of using * and then reference them by name (e.g. $row["Time"]) instead of number. It will make your code much more readable and protect it from changes in the database schema.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.