Sql Server转换日期

Question

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)

Answer 1

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');