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.

>>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: piegeacon@internetique.com) Thanks.