Just to recap - client had a database that was storing the dates of transactions - but - they wanted to be able to only enter a year, or just a year and month without an actual date. So, three columns were set up day|month|year and the values were being stored as integers ...
I ended up adding another column to the database (searchDATE) and am basically forcing a valid date value into it. When the client enters a new record - any value they leave blank in the date fields is still recording as a "0" in the day|month|year interger fields, but is converted into a "1", and then I'm using createdate to put it together in the searchDATE field. So, if they only enter 2007 and nothing else, 01/01/2007 is getting recorded into the searchDATE field. If only enter a month and year (which is what they seem to be doing most of the time) the day gets recorded as a "1". Presto - valid dates. Now their requested "search dates by range" feature works fine. Some of the other solutions posted look pretty cool though, and I'm playing with them just to learn the techniques used. Thanks for everybody that replied. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
