Query Date By Year

[cabobound]

I am trying to query a table on a date field by year where the user inputs the year.
The code I am using is...
sTr="Select * From table Where Right(BuyerPaidDate,4)='" & Request("yr") & "'"

The error I am getting is...
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I have tried many variations of this including cStr(Request("yr")). I am sure there are better ways to get this but I cannot find them and I am running out of time. Please help, thanks in advance.

K

 

[gmmastros]

wvdba,

Between queries are sargable, but in this case, the between operator wouldn't (necessarily) return the same data. If you look closely at the query I suggested, you'll see that I used >= and < which is not the same as >= and <=.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

gmmastros,
then why not change the dates and use - BETWEEN - like this:
Select * From table where BuyerPaidDate between '2008-01-01' And '2008-12-31'.
I'm not sure if SARGABLE makes any difference in this situation.
further, the only search arguement we have in this situation is user input of YEAR. now, you have to take it and make a complete date out of the YEAR. then constract a query based on two boundary dates. i just thought based on the statement of the scenario, the most straight forward thing would be to code this:

Select * From table where year(BuyerPaidDate) = Request("yr")

thanks.

 

[gmmastros]

If the BuyerPaidDate has a time component, the between statement you referenced would not return any rows for 12/31/2008 except those that occurred at exactly midnight.

Here's a blog that you may find interesting:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/only-in-a-database-can-you-get-1000-impr

Notice how the where clause criteria was modified in a similar way that I suggested. This caused a query that originally took 24+ hours to return in 36 seconds. That's a huge performance gain by simply modifying the code a little.

Many programmers attempt to take the simple route (as you suggested). They run the query and the performance is good, so they move on to the next task. It may be weeks, months, or even years later that the un-optimized query becomes a problem, because the tables have to grow in size before the performance becomes a problem.

The way I see it, I always *attempt* to write sargable queries, even if it's not needed. For example, a sargable query won't benefit you unless there is an index on the column. So... even if there is no index, I still write a sargable query because I may later decide to put an index on it. Or, I might be writing a query against a small table that doesn't need to be sargable because it's only a small number of rows and it will still be fast, but... you never know what's gonna happen to your tables. It's better to spend a couple extra minutes when you write the query, than to try and go back later and fix performance problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom