Finding all records based on Year 1

[sk1hotpepr]

I have a form that allows users to choose how far back they want to retrieve records based on number of years, which in turn will be sent to a stored procedure. I'm not sure how to filter records based on a date - X years. Can anyone lead me in the right direction? Thanks in advance!

 

[PWise]

year(datefield)>=year(getdate()-x)

 

[donutman]

Solution depends upon what you mean by a year. All of previous calendar years as pwise suggests or complete 12 months or maybe 365 days or maybe same date as last year? Is time of day important?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sk1hotpepr]

Any month/day within the year, time of day doesn't matter.

 

[donutman]

Pwise's solution will do that, e.g. for today and within 1 year will yield rows with date 1/1/2004 thru 12/31/2005 (inclusive).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sk1hotpepr]

Thank you both for your help - that worked!

 

[JamesLean]

I guess I ought to chip in with my usual warning on using functions when searching... ;-)

If you use a function on the column you are searching it will mean that any index on that column cannot be utilised. A better method may be to do something like this:

--get data for all of last year (2004 given current date of 15th March 2005)
WHERE date_col >= CAST(YEAR(GETDATE()) - 1 AS varchar) + '0101'
  AND date_col < CAST(YEAR(GETDATE()) AS varchar) + '0101'

--James

 

[donutman]

Good point James...I need to maintain awareness of that. A star for you.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]