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

[metaperl]

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.

[metaperl]

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

[Plamen Ratchev]

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