SQL Server Time Format is not equal to Client Side Time format

I’m trying to compare the client side time format ("14:00") to SQL Server time format ("14:00:00.0000000") with SQL Query, technically is not equal because SQL Server time format has seconds and milliseconds, so I tried to concatenate the seconds and milliseconds to client side time format in PHP script like this 14:00.":00.0000000" and yet is not equal to SQL Server time format.

My script in PHP:

// I assume that the $_POST['pAppointmentTime'] variable has a value of 14:00.
$pAppointTime = $_POST['pAppointmentTime'];                                      
$pAppointTime = $pAppointTime.":00.0000000";  

// SQL query 
"SELECT * FROM appointment WHERE 
   pAppointment_Date = '".$pAppointSchedDate."' ".
   "AND pAppointment_Time='".$pAppointTime."'".
   " AND Department='".$selectedDept."';"

So how can I compare these two with SQL Query and PHP Script?

Answer

I think that you may consider the following:

• Do not concatenate strings to build a statement and use parameters in your statements to prevent possible SQL injection issues.
• Pass date and time values as text using unambiguous date and time formats – yyyymmdd and hh:mm:ss.
• For your specific case, you may try to compare the date and time values using explicit cast and adding only seconds for the time values.

An example, that is a possible solution to your problem:

<?php
// Connection info
$server = 'serverinstance';
$database = 'database';
$uid = 'uid';
$pwd = 'pwd';

// Connection
try {
    $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die( "Error connecting to SQL Server".$e->getMessage());
}

// Parameters
$pAppointSchedDate = "date in yyyymmdd format";
$pAppointTime = $_POST['pAppointmentTime'];                                      
$pAppointTime = $pAppointTime.":00"; 
$selectedDept = "...";

// Statement
try {
    $sql = "
        SELECT * 
        FROM appointment 
        WHERE 
           CONVERT(date, pAppointment_Date) = CONVERT(date, ?) AND 
           CONVERT(time(0), pAppointment_Time) = CONVERT(time(0), ?) AND 
           Department = ? 
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(1, $pAppointSchedDate, PDO::PARAM_STR);
    $stmt->bindParam(2, $pAppointTime, PDO::PARAM_STR);
    $stmt->bindParam(3, $selectedDept, PDO::PARAM_STR);
    $stmt->execute();
    while ($row = $stmt->fetch( PDO::FETCH_ASSOC) ){
        echo print_r($row, true)."<br>";
    }
} catch (PDOException $e) {
    die ("Error executing query. ".$e->getMessage());
}

// End
$stmt = null;
$conn = null;
?>