Sql Server转换日期

I have a query that compare dates, something like:

SELECT * FROM TABLE WHERE MYDATETIME < @DATE

The column MYDATETIME is a DATETIME column but when I receive the date from the web form I receive it in an date format and create a DateTime object in php:

$date = DateTime::createFromFormat('d/m/Y',$date);

The problem is when the query compares the dates it uses the time of day as well, but when I create the date in php it gets the time of the system.

I would like to compare only the date, and not the time of day.

Which option is better for the database performance, creating the object in php using:

DateTime::createFromFormat('d/m/Y H:i', $date." 00:00");

or converting in the query?

SELECT * FROM TABLE WHERE CONVERT(DATE,MYDATETIME) < CONVERT(DATE, @DATE)

I think you are basically on track in your original question. Build a DateTime object, set the time component to 00:00:00 and then compare directly against MYDATETIME field in database.

That could be done as you propose or by doing something like this:

$datetime = DateTime::createFromFormat('d/m/Y',$date);
$datetime->setTime(0,0,0);
$date_string_for_db_compare = $datetime->format('Y-m-d H:i:s');

Mike is correct in stating that you need to pass the variable in the default format of the database field. This will give you the best performance from the query. Whenever you use a cast/convert function on the database field in the where clause indexing for that field will not be used. I would add to Mikes suggestion that you use >= and < so you can capture all transactions that happened during the 24 hour period.

See example below.

SELECT 
  FieldA
  FieldB,
  MyDateTime
FROM 
  TABLE 
WHERE 1=1
  and MyDateTime >= '2015-08-30 00:00:00'
  and MyDateTime <  '2015-08-31 00:00:00'

Hope this helps, Jason