Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 29th, 2003, 04:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default Index Part of Field

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
__________________
Regards
Owain Williams
  #2 (permalink)  
Old September 29th, 2003, 09:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old September 29th, 2003, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old September 29th, 2003, 09:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Datetime Field as index daworm MySQL 2 September 17th, 2006 11:33 PM
Extract part of field from inconsistent data Roly Reefer Access VBA 4 March 31st, 2005 09:25 AM
Seperating Date part from a datetime field ctranjith SQL Server 2000 2 October 25th, 2004 06:42 AM
The difficult part k0023382 Access 1 October 8th, 2004 03:37 AM
Index Part of Field owain Access 4 October 2nd, 2003 09:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.