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" <cf-talk@houseoffusion.com>
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" <cf-talk@houseoffusion.com>
>> 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:316123
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
