You're going to have to do that regardless of your storage format, because in a yearless structure the range 1101 to 301 doesn't make any sense. On the flip side, the ranges 101 to 301 and 1101 to 1231 do make sense, so that might be a better way to describe the subscription (internally, not externally). In any case, the conditional is going to have to exist somewhere, either to split the subscription up front, or to invert the range at query time.
cheers, barneyb On Tue, Dec 2, 2008 at 1:05 PM, Jim McAtee <[EMAIL PROTECTED]> wrote: > That won't work in cases where begindate > enddate. This is essentially a > seasonal newsletter signup, where the user is given a choice of the date > to begin and end the email. They might subscribe from Feb 12 to Jun 10, > but they might also subscribe from Nov 10 to Apr 5. The typical query > would be to select all records where today falls within the subscribed > date range. > > This can be done using a MySQL conditional, but it gets a little ugly. > > SELECT * > FROM newsletter > WHERE IF( begindate <= enddate, > '1202' BETWEEN begindate AND enddate, > '1202' >= begindate OR '1202' <= enddate ); > > > > ----- Original Message ----- > From: "Barney Boisvert" <[EMAIL PROTECTED]> > To: "cf-talk" <[email protected]> > Sent: Tuesday, December 02, 2008 12:31 PM > Subject: Re: Date Range Without Year > > >> Select all events between February 12th and June 10th: >> >> select * >> from eventTable >> where dateCol between 212 and 610 >> >> cheers, >> barneyb >> >> On Tue, Dec 2, 2008 at 11:14 AM, Jim McAtee <[EMAIL PROTECTED]> >> wrote: >>> That representation makes sense, but there needs to be a way to use it >>> to >>> construct SELECT queries using >=, <=, or BETWEEN. Using expressions >>> with >>> MySQL functions in the SELECT would be ok, if it can be done. >>> >>> >>> ----- Original Message ----- >>> From: "Barney Boisvert" <[EMAIL PROTECTED]> >>> To: "cf-talk" <[email protected]> >>> Sent: Tuesday, December 02, 2008 11:52 AM >>> Subject: Re: Date Range Without Year >>> >>> >>>> You could just use numbers to represent the dates. Year runs from 101 >>>> to 1231 (thats MDD), and easily create from a date like this: >>>> #month(now()) * 100 + day(now())#. Strings would also work instead of >>>> numbers: "0101" to "1231", and created via #dateFormat(now(), >>>> "mmdd")#. >>>> >>>> cheers, >>>> barneyb >>>> >>>> On Tue, Dec 2, 2008 at 10:34 AM, Jim McAtee <[EMAIL PROTECTED]> >>>> wrote: >>>>> The DBMS that I'm using is MySQL 5.0. >>>>> >>>>> I have a table where I want to store begin and end dates _without_ a >>>>> year. >>>>> For example, 'Dec 4' through 'Jun 15'. How can I best represent this >>>>> in >>>>> the database? >>>>> >>>>> I'll be doing selects of the nature >>>>> >>>>> SELECT * >>>>> FROM mytable >>>>> WHERE #today# BETWEEN begindate AND enddate > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:316124 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
