Re: Date Range Search Problem - when it's integers stored and not dates - My Solution
So, if they only enter 2007 and nothing else, 01/01/2007 is getting recorded Well, this will be fine when looking for events = 06/30/2007 for instance, since 0/0/2007 stands for any date in 2007. but what if looking for events = 06/302007? 01/01/2007 won't come out and it should, since it could be any date in 2007, including past June. If you take this approach, you should have two pseudo dates: one for the lower bound, another one for the upper. Have you tried my SQL expression? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302105 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
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
Re: Date Range Search Problem - when it's integers stored and not dates
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
Re: Date Range Search Problem - when it's integers stored and not dates
Ok, how about this, a bit ugly, but should work on any database: WHERE (YEAR #form.from_y# OR (YEAR = #form.from_y# AND (MONTH = 0 OR (MONTH = #form.from_m# AND (DAY = 0 OR DAY = #form.from_d#) AND (YEAR #form.to_y# OR (YEAR = #form.to_y# AND (MONTH = 0 OR (MONTH = #form.to_m# AND (DAY = 0 OR DAY = #form.to_d#) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:302008 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
Lez, this has been asked, but not answered. What do the zeros mean? Do they indicate a date range? Does 2007.0.0 mean the entire year of 2007? Does 2007.10.0 mean the entire month of October in 2007? Does 0.0.0 mean always? If so, I'd create two date columns, begin_dt and end_dt. I'd then write SQL scripts to populate those columns. On Tue, Mar 25, 2008 at 9:51 AM, Claude Schneegans [EMAIL PROTECTED] wrote: Ok, how about this, a bit ugly, but should work on any database: WHERE (YEAR #form.from_y# OR (YEAR = #form.from_y# AND (MONTH = 0 OR (MONTH = #form.from_m# AND (DAY = 0 OR DAY = #form.from_d#) AND (YEAR #form.to_y# OR (YEAR = #form.to_y# AND (MONTH = 0 OR (MONTH = #form.to_m# AND (DAY = 0 OR DAY = #form.to_d#) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:302012 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates - My Solution
Just to recap - client had a database that was storing the dates of transactions - but - they wanted to be able to only enter a year, or just a year and month without an actual date. So, three columns were set up day|month|year and the values were being stored as integers ... I ended up adding another column to the database (searchDATE) and am basically forcing a valid date value into it. When the client enters a new record - any value they leave blank in the date fields is still recording as a 0 in the day|month|year interger fields, but is converted into a 1, and then I'm using createdate to put it together in the searchDATE field. So, if they only enter 2007 and nothing else, 01/01/2007 is getting recorded into the searchDATE field. If only enter a month and year (which is what they seem to be doing most of the time) the day gets recorded as a 1. Presto - valid dates. Now their requested search dates by range feature works fine. Some of the other solutions posted look pretty cool though, and I'm playing with them just to learn the techniques used. Thanks for everybody that replied. ~| 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:302054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates - IDEA!!!
On Mon, Mar 24, 2008 at 7:26 PM, Les Mizzell [EMAIL PROTECTED] wrote: ... What's the easiest way to search for values in a list starting at position X and ending at position Y? Off the top of my head, I'd do a listSort, and then iterate. Dunno if the list position idea would work for the search tho-- that'd be some really intensive looping there. :denny -- ~| 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:302055 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
On Mon, Mar 24, 2008 at 7:29 PM, Les Mizzell [EMAIL PROTECTED] wrote: denstar wrote: Are they against letting you add a real date field? Seeing there's already several hundred records in the database, some with dates, some with NONE - which is exactly what they want - adding a real date field would be tough. It was a hard argument to start with, and I let them win. They won, they get to figure it out. :-) Seriously tho, when I've been in that position, I've done the following: No dates become nulls 0/0/2008 becomes 1/1/2008 12/0/2008 becomes 12/1/2008 If they want whole years spanned, months, etc. do what Sonny suggests, and add a second end date field. If you go with the end date, you can put whatever logic they want in there. If only the year is specified, have it span the whole year 0/0/2008 becomes 1/1/2008 and 12/31/2008. ditto for months: 12/0/2008 becomes 12/1/2008 and 12/31/2008. And that's logic you could add to the entry form's save routine, so future events will have correct dates too, without having to change the way they're inputting them now. But - see my other email. I've had an idea while watching House!! Sometimes getting out from in front of the computer is the best way to find a solution (maybe - haven't tried it yet)!! Oh yeah. Breaks are good for the noggin, and water doesn't hurt either. :-) -denny -- ~| 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:302056 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
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
Re: Date Range Search Problem - when it's integers stored and not dates
The idea being to parse the separate date columns into usable dates in the subquery, and then join that to the superquery as needed. I'd been trying to come up with a way to do this, but the columns with a 0 in them are throwing me off, since 0 isn't a valid date value. ~| 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:301965 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
I'd been trying to come up with a way to do this, but the columns with a 0 in them are throwing me off, since 0 isn't a valid date value. and I might add - it was put a 0 in there, or allow a null, which would have been worse... ~| 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:301966 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
How about: and YEAR = #form.from_y# and YEAR = #form.to_y# and ((MONTH = #form.from_m# and MONTH = #form.to_m#) OR MONTH=0) and ((DAY = #form.from_d# and DAY = #form.to_d#) OR DAY=0) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:301967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
I'd been trying to come up with a way to do this, but the columns with a 0 in them are throwing me off, since 0 isn't a valid date value. You could use CASE or IF to fill in dummy values for those zeroes, but then, if you substituted 1 (January) for the month for example, it wouldn't be found in a date range search from Feb - April for example. If a record only has the year entered, for example 2007, I suppose you'd want to return it for any search that had 2007 in the date range. So maybe you could do a conditional check for that in the subquery, and then return any of those records that match the year -- something like CASE WHEN day = 0 and month = 0 and year 0 then 'true' else 'false' END AS 'matchonlyyear' Then maybe do a query of queries or something to check for those year only records. All I can say is you are going to have to get super creative and pull out all the stops. -- Josh ~| 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:301968 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
Claude Schneegans wrote: How about: and YEAR = #form.from_y# and YEAR = #form.to_y# and ((MONTH = #form.from_m# and MONTH = #form.to_m#) OR MONTH=0) and ((DAY = #form.from_d# and DAY = #form.to_d#) OR DAY=0) The problem with that is the same problem as the previous example I posted though. Try searching for dates between 10/15/2006 and 04/14/2007 See the problem? Yup, I've scr*wed myself on this one I believe! ~| 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:301969 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
Are they against letting you add a real date field? I've been there, in spades, and there's always a way through. Sometimes it's a lot easier, and not as much fun, as banging your head against the same angle of attack for hours tho. :-) Perhaps add a date field, and then make some magic that turns those integers into dates. And THEN do the query. Not as cool as one fell swoop (although if you're good at your SQL dialect, crazy queries like that ARE possible), but still. force be with you, :denny On Mon, Mar 24, 2008 at 6:07 PM, Les Mizzell [EMAIL PROTECTED] wrote: Claude Schneegans wrote: How about: and YEAR = #form.from_y# and YEAR = #form.to_y# and ((MONTH = #form.from_m# and MONTH = #form.to_m#) OR MONTH=0) and ((DAY = #form.from_d# and DAY = #form.to_d#) OR DAY=0) The problem with that is the same problem as the previous example I posted though. Try searching for dates between 10/15/2006 and 04/14/2007 See the problem? Yup, I've scr*wed myself on this one I believe! ~| 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:301972 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates - IDEA!!!
Ahhh- here's an idea! OK, in theory... Let's just look at just the month for starters... form.startMONTH form.endMONTH Each has a value of 1 - 12 but, let's use that as LIST START AND END POSITIONS Set a list containing 2 years worth of months: cfset findMONTHS = 1,2,3,4,5,6,7,8,9,10,11,12,1,2,3,4,5,6,7,8,9,10,11,12 form.startMONTH would equal the starting position in the list (form.endMONTH + 12) would be the ending position in the list Search for anything in between. How's that? What's the easiest way to search for values in a list starting at position X and ending at position Y? ~| 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:301973 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
denstar wrote: Are they against letting you add a real date field? Seeing there's already several hundred records in the database, some with dates, some with NONE - which is exactly what they want - adding a real date field would be tough. It was a hard argument to start with, and I let them win. Perhaps add a date field, and then make some magic that turns those integers into dates. Yea - but how do you turn 0/0/2007 into a valid date? And *what* date do you turn it into if you replaced the 0 values with something else? And what if they don't enter a year at all? 0/0/0??? Ack! But - see my other email. I've had an idea while watching House!! Sometimes getting out from in front of the computer is the best way to find a solution (maybe - haven't tried it yet)!! ~| 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:301974 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Range Search Problem - when it's integers stored and not dates
The problem with that is the same problem as the previous example I posted though. Try searching for dates between 10/15/2006 and 04/14/2007 Given those search dates, how do you want to handle records with a year only (2006 or 2007). Same question for the month. ~| 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:301976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Range Search Problem - when it's integers stored and not dates
Try searching for dates between 10/15/2006 and 04/14/2007 See the problem? Yeah, my answer was a bit too easy ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:301978 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
