469,927 Members | 1,946 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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:

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 1641
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,


Plamen Ratchev

Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by David | last post: by
1 post views Thread by rinmanb70 | last post: by
1 post views Thread by Mike Heywood | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.