CODEWITHSUNDEEP
sqlms-access
J.Olufsen
J.Olufsen

Reputation: 13915

MS Access SQL to Select date range

I need to select a record with dates which has dates ( in range: form 1998 to 1999). I wrote the statement which did seem to work . Why?

SELECT *
FROM Factory
WHERE 
(EXTRACT(YEAR FROM date) AS dyear) BETWEEN '1998'  AND '1999'

Upvotes: 2

Views: 13308

Answers (2)

HansUp
HansUp

Reputation: 97101

Applying the Year() function for every row in Factory will be a noticeable performance challenge if the table includes thousands of rows. (Actually it would be a performance challenge for a smaller table, too, but you would be less likely to notice the hit in that case.) A more efficient approach would be to index the [date] field and use indexed retrieval to limit the db engine's workload.

SELECT f.*
FROM Factory AS f
WHERE f.date >= #1998-1-1# AND f.date < #2000-1-1#;

Whenever possible, design your queries to take advantage of indexed retrieval. That can improve performance dramatically. As a simplistic rule of thumb: indexed retrieval = good; full table scan = bad. Try to avoid full tables scans whenever possible.

Upvotes: 2

John Woo
John Woo

Reputation: 263693

You can use YEAR() to get the year from the date.

SELECT *
FROM   Factory
WHERE  YEAR(date) BETWEEN 1998 AND 1999

Upvotes: 4

Related Questions