Index Part of Field

owain · September 29th, 2003, 04:19 AM

I have placed a post in the Access forum entitled Index Part of Field and I am posting it here because even though it is specific to Access, I know that most of the really clever database guys live in this forum. Please accept my apologies for the duplicate post.

Quote:

quote:The original post reads:I have a Date/Time field that I want to index, however I only want to index the Date part of the field and not the time part.

Is there any way of doing this (in VB DAO)?

The original post is available at p2p.wrox.com/topic.asp?TOPIC_ID=4651

Regards
Owain Williams

Jeff Mason · September 29th, 2003, 09:04 AM

Can't be done. A Datetime datatype is type which represents both a date and a time. You can only define an index on a whole column (or columns), not part of one.

I'm curious; what are your trying to accomplish?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · September 29th, 2003, 09:17 AM

I am afraid it would take too long to explain. One of my colleagues asked if it could be done and I decided to delegate the question to the masters of the database universe.

He had the idea that using a partial column index was the way to solve his problem, now that we know it is impossible we are working on a better (and normalised!) way of getting round (or removing) the problem.

Regards
Owain Williams

Jeff Mason · September 29th, 2003, 09:37 AM

Yeah, I was about to jump all over you about database normalization and atomic data. :D Before I did, though, I wanted to hear what you thought your problem was.

Note that it's perfectly reasonable to define an index on a datetime column. Then, queries with WHERE clauses on that column will tend to use the index, and this use can be quite efficient, especially when the BETWEEN operator is utilized...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com