473,321 Members | 1,748 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,321 software developers and data experts.

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_date that is indexed. I would like to find all records in
this table where last_update_date 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_date ) = CONVERT(INT, SUBSTRING('$
{START_DATE}', 5, 2))
AND YEAR( last_update_date ) = 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 1791
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_date 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
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...
2
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...
13
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...
1
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...
3
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...
1
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...
1
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...
8
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...
1
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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: 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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.