473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Indexes & Dates

Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_date,'mon') on that field, the index is not used(naturally), and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)
Jun 5 '07 #1
4 1447
frozenmist
179 Expert 100+
Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_date,'mon') on that field, the index is not used(naturally), and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)
Hi,
Please take more care while posting threads. You had posted a duplicate thread, which I have deleted.
Thank you...

Moderator
Jun 5 '07 #2
Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_date,'mon') on that field, the index is not used(naturally), and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)

Hi All,
I found out a solution. It seems to be very simple, but seems to be very effective.
If I like to found out records entered in the month of January 2007, I can easily do so by finding out records which are after 31st Dec 2006, but before 1st Feb 2007. In this way, I won't have to use a function on the date field, thereby enabling the compiler to use the index on that field
Jun 7 '07 #3
debasisdas
8,127 Expert 4TB
Try using the operator BETWEEN for the purpose.
Jun 7 '07 #4
Yes, that is alright. Actually, my main concern was to use indexes, which is not possible if I operate to_char on the date column .....
Jun 8 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: J P Singh | last post by:
Hi All I am trying to query a database with a combination of surname and date of birth but it is giving me wrong results in certain conditions. It is the mm/dd/yyyy and dd/mm/yyyy stuff that...
8
by: tom | last post by:
hello lads - got a problem; I made a file that should write down a report into the excel cells. the issue is not coming from the sql or summat, but from excel file cannot convert the date...
0
by: Paul | last post by:
I'm confused about something I did this week. I have an 8.1.7.4 database with tables partitioned by dates. I recently dropped my maxvalue tablespace, added a few more partitions for up-coming dates,...
0
by: Massimo Fiorentino | last post by:
Hello there! I am a bit of a newbee into the mySQL world and I have a question regarding switching from one DB to another. I have for a couple of years used a very simple CMS-system created by...
0
by: K Finegan | last post by:
I have an archival process on a large database that runs once a month. At the beginning of the process the triggers and indexes on the tables whose data is moved are dropped, the data is moved and...
4
by: kinne | last post by:
The following code is supposed to reverse the date in "yyyy-mm-dd" format, but it produces different results in Firefox 1.0 and in Internet Explorer 6SP1. In Firefox, the result is correct...
2
by: p175 | last post by:
People, I have an ESE 8.2.2 database running on win2k server . I need to do some system testing that requires I reset the dates back a couple of years then progressivily move it forward to...
2
by: Jon | last post by:
I have an asp.net app that I've localized using satellite assemblies. It works wonderfully, even for displaying dates. My problem comes when I try to use one of those dates in a SQL query. I...
0
by: Vijay | last post by:
Prep Courses for International Certifications, CSTE & CSQA & ISEB & ISTQB &Business Analyst & SOA Certifications in HYDERABAD. After receiving overwhelming response to our last 50+ batches, ...
0
by: Medhatithi | last post by:
I have a table whose indexed column is a char(16) field. I am giving the schema. CREATE TABLE Emp(Empno char(16), Ename varchar(25), ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.