Convert SQL server datetime fields to compare date parts only, with indexed lookups

Solution 1:

The best way to strip the time portion of a datetime field is using datediff and dateadd functions.

   DateAdd(day, datediff(day,0, MydateValue), 0)

This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day "0" - (1 jan 1900), and the second one which represents the number of ticks (each tick is about 3.33 ms) since midnight (for the time) *.

the formula above simply has to only read the first integer. There is no conversion or processing required, so it is extremely fast.

To make your queries use an index... use this formula on the input filtering parameters first, or on the "other" side of the equal sign from the tables date time field, so that the query optimizer does not have to run the calculation on every datetime field in the table to determine which rows satisfy the filter predicate. This makes your search argument "SARG-able" (Search ARGument)

Where MyDateTimeColumn > DateAdd(day, 
      datediff(day,0, @MydateParameter), 0)    -- SARG-able

rather than

Where DateAdd(day, datediff(day,0, 
      MyDateTimeColumn ), 0) > @MydateParameter -- Not SARG-able

* NOTE. Internally, the second integer (the time part) stores ticks. In a day there are 24 x 60 X 60 X 300 = 25,920,000 ticks (serendipitously just below the max value a 32 bit integer can hold). However, you do not need to worry about this when arithmetically modifying a datetime... When adding or subtracting values from datetimes you can treat the value as a fraction as though it was exactly equal to the fractional portion of a day, as though the complete datetime value was a floating point number consisting of an integer portion representing the date and the fractional portion representing the time). i.e.,

`Declare @Dt DateTime  Set @Dt = getdate()  
 Set @Dt = @Dt + 1.0/24  -- Adds one hour  
 Select @Dt  
 Set @Dt = @Dt - .25 -- Moves back 6 hours  
 Select @Dt`

Solution 2:

Converting numeric types to string values (a type of Boxing) is not the best performing method of doing what you are looking for. Its not really about index-able, because the actual column type is date time.

If you are looking for the best way query for dates, then your example is right, but you may want to take into account the 3 ms precision difference in MSSQL. It can mean that records from one day can show up in another day's result.

This

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'

Should be this

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'

Solution 3:

It's correct - doing the conversion will execute the conversion for every row queried. It's better to leave the date columns as dates, and pass in your where clauses as dates:

select * from appointments where appointmentdate between 
'08/01/2008' AND '08/16/2008'

Note: Leaving off the time means midnight (00:00.000), so you will include all times for 08/01, and all times from 08/15, and anything that is exactly 08/16/2008 00:00:00

Solution 4:

Have a computed persisted column calculate the expression you need. If columns are computed and persisted, they can also be indexed.