Sql Server convert date

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)

This can be a serious design flaw. Leave your db with the datetime and have the webform push a datetime. Boom done — FirebladeDan, Aug 25 '15 at 19:41
I need the datetime for other reasons in the table, but the field in the webform must be Date, because I want to get records for an specific range of dates, ignoring the time — Marco Prado, Aug 25 '15 at 19:45
If you are going with database option ( which I think is good ), make sure you have the right indexes created there. If you have index on MyDateTime, then converting it will not make of those indexes. — Sagar, Aug 25 '15 at 21:49

score 0 · Accepted Answer · answered Aug 26 '15 at 13:55

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

score 0 · Answer 2 · answered Aug 30 '15 at 02:53

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

Sql Server convert date

2 Answers2