473,886 Members | 2,437 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

optimal selection of all records for a certain month (illegal datesOK?)

First a simple question: Is it allowed to provide a date range that
has an illegal date. For instance

SELECT * FROM tbl WHERE datex >= 2007-09-01 AND datex <= 2007-09-31

Now for my real question. I have a table with a column named
last_update_dat e that is indexed. I would like to find all records in
this table where last_update_dat e has a certain year and month.

The actual date which has the year and month is an in a programming
language string, so I am doing substring operations to find the proper
month and day:

SELECT
*
FROM
xfer_tbl
WHERE
AND MONTH( last_update_dat e ) = CONVERT(INT, SUBSTRING('$
{START_DATE}', 5, 2))
AND YEAR( last_update_dat e ) = CONVERT(INT, SUBSTRING('$
{START_DATE}', 1, 4))

But as you can see, this does not take advantage of the databases (MS
SQL 2000) indexing power does it? It would be better to use >= and <=
as in my first sample query, but then I run the risk of using an
illegal date.
Jun 27 '08 #1
2 1815
On May 8, 2:09 pm, metaperl <metap...@gmail .comwrote:
First a simple question: Is it allowed to provide a date range that
has an illegal date.
No it isn't:

SELECT * FROM fut WHERE last_update_dat e BETWEEN '2007-09-01' AND
'2007-09-31'; -- ERROR
Jun 27 '08 #2
As you already found out invalid dates will result in error. Not sure why
you sent the dates in string format. The best is to pass dates as date and
time data type parameters to avoid any conversion.

One way to utilize indexes is to pass the start date (since that is always
the first of the month) and then calculate the end of the month (or rather
the first of the next month and use < to compare). It could look like this:

SELECT <columns>
FROM Table
WHERE datex >= '20080501'
AND datex < DATEADD(month, DATEDIFF(month, 0, '20080501') + 1, 0);

That way you do not have to worry about issues with the end date. And if you
always pass the first of the month, you can simplify to DATEADD(month, 1,
'20080501').

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1551
by: Gary | last post by:
I am working with a report generator that is based on SQL Server 2000 and uses ASP as the UI. Basically we have a set of reports that end users can execute through a web browser. In general the model works fine, but we are running into some scaling issues. What I'm trying to determine is, what is the optimal configuration for this system. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. We have been using the "fixed"...
2
1501
by: David | last post by:
Hi, Is this possible & How ? We manufacture products. Each product has a list of items which can be packed at our production facility. A Picking List. Certain customers may require certain packing items to be omitted from certain products. My products table is linked to a ProductPacking Table 1 to Many.
13
5860
by: Sue | last post by:
I'm working on a database that keeps track of employees hired by a general contractor on a project by project basis. These employees are hired to work on a project and are then laid off either at the end of the work they were hired to do or at the end of the project. Any employee may be hired, laid off and rehired several times during the course of time. The tables I have and the critical fields to my question are: TblEmployee EmployeeID...
1
1731
by: RobMea | last post by:
I am trying to find a solution to the following problem. First off I'm new to Access and SQL but have a good back ground in other languages, thus at a new job this has just been dumped on me so as is my style I agreed figuring that over time it's just another language and style anyway to add to the others I've worked in. Heres my situation. Have used a make table query to establish a Temp historical archive table for reporting...
3
2195
by: Eddie901 | last post by:
I have a date field in a table formatted as a "Medium Date", e.g., 29-Mar-06. When I filter by that selection it's okay when the date is after the 13th of the month, but for the 12th or below it transposes the month with the day, so if I filter by 1-Feb-06, I get those records from 2-Jan-06. Anything I can do about this? Thanks for any help. Rachel Bourne
1
1050
by: rinmanb70 | last post by:
I have a table of checking transactions. Some are past, some from today, and some are dated with future dates (such as recurring payments). Is there a way to set up a query for my balance (credits minus debits) that will exclude transactions that occur after upcoming paydays (1st and 15th) dynamically? For instance, if I have a transaction scheduled for the 2nd of next month (after payday on the 1st) I'd like to be able to separate it...
1
2339
by: Mike Heywood | last post by:
Hi, I am currently trying to automate a process that I have been studying the manual results from for a while. The process simply identifies events that meet certain criteria and at the moment it then requires human interaction to decide what to do next (is a seperate process run or not). I have set up the process to do this automatically based on a couple of variables calculated but I am having a few problems optimising the data to...
8
2980
by: lulu123 | last post by:
I have a database, which keep track of the hours employee worked. The employee enter the hours monthly. The table is set to like, Project/ Month/ Hour/ what i want to do is: I want to lock the record based on the month. if i make a form with a month combo box. ..I want all of the record to be locked except the record that has the month matching the selected month. so, when employee enter their hours, they can only edit or add record to...
1
4748
by: annemariearmour | last post by:
I am using Crystal reports version 11.2 to create reports. The data source is SQL Server, and I am using views rather than reporting directly from tables. I apply selection criteria to the incoming records at record level and at group level. For example, I may select all bill lines with a receipt date within a certain date range. This results in a list of bill lines within that date range. The records are grouped and values on the records...
0
9956
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7139
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5808
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6010
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4627
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.