You might be able to join the table to itself in a query, and on that join
concat your date integers into actual date values.
Something like...
SELECT whatever
FROM thetable t
INNER JOIN ( SELECT id, CONCAT(yearcol,'/',monthcol,'/',daycol) AS gooddate
FROM thetable
) gooddates
ON t.id = gooddates.id
WHERE gooddates.gooddate BETWEEN datea AND dateb
The idea being to parse the separate date columns into usable dates in the
subquery, and then join that to the superquery as needed.
That syntax may or may not work in your db, the example above would be for
MySQL.
-- Josh
----- Original Message -----
From: "Les Mizzell" <[EMAIL PROTECTED]>
To: "CF-Talk" <cf-talk@houseoffusion.com>
Sent: Monday, March 24, 2008 4:06 PM
Subject: Date Range Search Problem - when it's integers stored and not dates
> 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:301964
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
