Geek Answers Handbook

Choose from a daily table

So, I have a table in SQL Server with a datetime column. I want to select everything from this table:

 select * from dbo.tblMessages

but I want to pass the datetime parameter. Then I want to select all messages from the table with the same day as the datetime column in tblMessages , and not only those that were sent in the last 24 hours, etc.

How can I do it?

Thanks.

+4
source share
3 answers

This should use the index in 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 the proper use of the index, including the DATEDIFF between this and @Mydatetime

+4
source

How are you on SQL Server 2008 you can just do

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

It is possible. SQL Server will add ComputeScalar to the plan, which calls the GetRangeThroughConvert internal function and gets the start and end of the search range.

+1
source

If you need to do this a lot, and if you are on SQL Server 2005 or newer, you can also do this:

  • add three calculated columns for the day, month, year of your date and save those

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

  • enter the index in three columns:

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

  • Now you can easily and quickly search in those days, months, a year and an index, your search will be very quick and fast.

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

With this setting — three calculated, saved columns — you can now simply insert new rows into the table — these three columns will be calculated automatically.

Because they are stored and indexed, you can search these columns easily and very efficiently.

And with such flexibility, you can also easily find, for example. all rows for a given month or year are very beautiful.

0
source

More articles:


All Articles