Select from table given day

So I have a table in SQL Server with a datetime column on it. I want to select all from this table:

select * fr开发者_Go百科om dbo.tblMessages

but I want to pass in a datetime parameter. Then I want to select all messages from the table that have the same day as the datetime column in tblMessages, not just ones posted in the past 24 hours, etc.

How would I do this?

Thanks.

This should use an index on MyDateTimeCol in tblMessages

  select * from dbo.tblMessages
  WHERE

  MyDateTimeCol >= DATEADD(day, DATEDIFF(day, 0, @Mydatetimeparameter), 0)
  AND
  MyDateTimeCol < DATEADD(day, DATEDIFF(day, 0, @Mydatetimeparameter), 1)

Any function applied to MyDateTimeCol will prevent an index being used correctly, includin DATEDIFF between this and @Mydatetime

As you are on SQL Server 2008 you can just do

SELECT *
FROM tblMessages
WHERE CAST(message_date AS DATE) = CAST(@YourDateParameter AS DATE)

This is sargable. SQL Server will add a ComputeScalar to the plan that calls the internal GetRangeThroughConvert function and gets the start and end of the range to seek.

If you need to do this a lot, and if you're on SQL Server 2005 or newer, you could also do this:

• add three computed columns for the day, month, year of your date and persist those

  ALTER TABLE dbo.YourTable
    ADD DayPortion AS DAY(YourDateTimeColumn) PERSISTED
  -- do the same for MONTH(YourDateTimeColumn) and YEAR(YourDateTimeColumn)
  

• put an index on the three columns:

  CREATE NONCLUSTERED INDEX IX_DatePortions
    ON dbo.tblMessages(YearPortion, MonthPortion, DayPortion)
  

• now, you can search very easily and quickly for those days, months, year, and with the index, your search will be very performant and quick

  SELECT (list of columns) 
  FROM dbo.tblMessages
  WHERE YearPortion = 2011 AND MonthPortion = 4 AND DayPortion = 17
  

With this setup - three computed, persisted columns - you can now simply insert new rows into the table - those three columns will be calculated automatically.

Since they're persisted and indexed, you can easily and very efficiently search on those columns, too.

And with this flexibility, you can also easily find e.g. all rows for a given month or year very nicely.