Select from table given day

Question

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

select * from 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.

Are you sure this is a `timestamp` column and not a `Date` or `DateTime` column? `timestamp` columns do not hold temporal information. http://msdn.microsoft.com/en-us/library/aa260631(v=sql.80).aspx — Oded, Apr 17 '11 at 19:52

gbn · Accepted Answer · 2011-04-17T20:06:35.563

4

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

edited Apr 17 '11 at 20:06

answered Apr 17 '11 at 19:59

gbn

422,506
82
585
676

How does the `0` work in your DATEDIFF function? I thought that had to be the start date? – Abe Miessler Apr 17 '11 at 20:04
@Abe Miessler: I'm removing time from datetime. 0 = 01 Jan 1900 in this case. See http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime – gbn Apr 17 '11 at 20:06
1

In SQL Server 2008 [casting to date is sargable](http://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable). – Martin Smith Apr 17 '11 at 20:32

score 1 · Answer 2 · answered Apr 17 '11 at 20:50

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.

score 0 · Answer 3 · answered Apr 17 '11 at 20:25

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.

Select from table given day

3 Answers3