This is ugly and uses T-SQL syntax but you get the idea. SELECT id FROM ( SELECT id, CAST( CAST(year AS varchar) + '-' + CAST(CASE month WHEN 0 THEN 1 ELSE month END AS varchar) + '-' + CAST(CASE day WHEN 0 THEN 1 ELSE day END AS varchar) AS datetime) as realdate FROM aaa ) tmpT WHERE realdate BETWEEN '2006-01-01' AND '2007-05-05'
On Mon, Mar 24, 2008 at 4:06 PM, Les Mizzell <[EMAIL PROTECTED]> wrote: > I've got a database in which dates are stored as integers in three > separate columns as: day|month|year > > This was done because the client wished to create records that could > contain only a year, or just a year and month, or year, month, and day, > or no date at all. Values in the columns default to "0" if nothing was > entered. > > Bad idea. Really bad idea! Worked great at first, but as 'feature creep" > has slowly reared its ugly head, this way of storing dates has caused me > no end of headaches. They want to add a date search by range. So, you'd > want to do something like: > > and YEAR >= #form.from_y# and YEAR <= #form.to_y# > and MONTH >= #form.from_m# and MONTH <= #form.to_m# > and DAY >= #form.from_d# and DAY <= #form.to_d# > > So, let's say you have four records > > day|month|year > 15 | 05 |2005 > 16 | 04 |2006 > 15 | 03 |2007 > 0 | 01 |2006 > > > Uh oh - that's not going to work. For example, searching for dates > between 05/15/2006 and 04/16/2007 is looking at *integers* in the month > column greater than 5 *and* less than 4, plus integers in the day column > greater then 15 but less than 16. Whoops, no results returned. > > I've gotten myself into this mess. Is there any trick I can use in the > queries to get myself out, or am a @[EMAIL PROTECTED] > > I should have NEVER let them talk me into letting them enter records > without *complete* dates - but, the client is always right, huh? But, > that was almost a year ago and they weren't asking for a date range > search at the time either.. > > Argh! > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:301990 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
