I want to second Charlie's note on losing the index. You have to be careful with data manipulation you do on the left side of your where statements.
If you have a table with millions of dates, the year function has to be applied to every single date in the table. If you are using MS SQL, I personally would do: where date_column >= dateadd(yy,-1,getdate()) If your column is not a datetime datatype, but a string, then insert obligatory lecture here on using incorrect datatypes. You can probably cast it to a date like cast(date_column_stored_unscrupulously_as_string as datetime) but that cast will have to happen to every single record. If it is a string, the "LIKE" would be your best bet, but keep in mind, any index is thrown out the window again when you have a wild card at the start of your search string as in '%09'. ~Brad ----- Original Message ----- From: "Charlie Griefer" <[EMAIL PROTECTED]> To: "cf-talk" <cf-talk@houseoffusion.com> Sent: Thursday, October 16, 2008 12:48 AM Subject: Re: Pulling only 1 year of records > On Wed, Oct 15, 2008 at 10:38 PM, Phillip M. Vector < > [EMAIL PROTECTED]> wrote: > >> How do I write a query to only pull 1 year (2009 lets say) from the DB >> that has a date field? >> >> would I do something like >> >> where datestarting like "%09" > > WHERE year(myDateField) = 2009 > > but you lose any indexing that you might have on that column. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313993 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
