How to make a selection from MS SQL database by datetime field?

Question

PHP

I'm trying to get table data from MS SQL Server, the column is in datetime format and I seem to substitute everything correctly, but I still can't get sorted data.

spoiler

php

<?php
                $serverName = ''; // server name
                $cinfo = array(
                    "Database" => "", // database
                    "UID"=>"", // user
                    "PWD"=>"" // pass
                );
                $conn = sqlsrv_connect($serverName, $cinfo);

                if ($conn){
                    echo "connected"."<br/>";
                    $datetimenow = current_time("d.m.Y H:i"); // функция wordpress
                    echo $datetimenow; // 30.05.2020 14:10
                if(($result = sqlsrv_query($conn,"SELECT * FROM dbo.GorElectroTrans WHERE GPS_datetime=$datetimenow")) !== false){
                        while( $obj = sqlsrv_fetch_object( $result )) {
                              echo $obj->Longitude.'<br />';
                        }
                    }
                }else{
                    die(print_r(sqlsrv_errors(), true));
                }
            ?>

Answer 1

DECLARE @T DATETIME = '30.05.2020 14:10'
SELECT @T
SELECT CONVERT( VARCHAR, @T, 104 ) + ' ' + LEFT( CONVERT( VARCHAR, @T, 8 ), 5 )
SELECT FORMAT( @T, 'dd.MM.yyyy HH:mm' )

2020-05-30 14:10:00.000
30.05.2020 14:10
30.05.2020 14:10
That is, the representation DATETIMEon your form is different from the raw value. You need to format the left side too:

…
SELECT * 
  FROM dbo.GorElectroTrans 
  WHERE FORMAT(GPS_datetime, 'dd.MM.yyyy HH:mm' ) = $datetimenow
…

And if you need to compare with the current moment, remove the parameter.

…
SELECT * 
  FROM dbo.GorElectroTrans 
  WHERE GPS_datetime = GETDATE()
…

Or without seconds:

…
SELECT * 
  FROM dbo.GorElectroTrans 
  WHERE CAST( GPS_datetime AS SMALLDATETIME ) = CAST( GETDATE() AS SMALLDATETIME )
…