Les Mizzell 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.
In SQL that is easy with a row-wise comparison: WHERE ROW(2008, 3, 25) < ROW(year, month, day) AND ROW(2008, 3, 28) > ROW(year, month, day) The problem is your rows with a 0 in them. Under which conditions should they match? Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:301992 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
