[RESOLVED] Parsing Date Field for Searching

tcarnes

I have a form that includes entries for dates which I'd like to use to do searching on a database.

I want the user to be able to input either a date, a partial date, several noncontiguous dates or partial dates, or a range of dates. Basically I'd like to be able to recognize anything (within reason) that might be useful for searching a date field on.

Has anyone already done this? I'm hoping to keep from having to reinvent the wheel if someone has already done this.

Terry

barand

Are there any rules that the user must adhere to?

EG
multiple dates separated by commas.
for a range enter "date1 to date2".

And how do you interpret 06/01 (Jun 1 or Jan 6) if users are multinational?

tcarnes

barand wrote:
Are there any rules that the user must adhere to?

So far it's pretty much up to me. From my understanding, our users are used to "1995" to mean anything within that year, "199203" meaning anything in March 1992, and "19920314" to mean the specific day.

I would like to be able to give the users more than what they are used to. Since dates can be fairly non-standard, I'd like to be able to try and handle anything they try to throw at me.

Your question:

barand wrote:
And how do you interpret 06/01 (Jun 1 or Jan 6) if users are multinational?

is a good point. Since the vast majority of our users are in the States, I think we'll call 06/01 Jun 1 and I'll have to state that, but beyond that I would like to try and handle as much as I can.

Terry

barand

You could try something along these lines

<?php
function myDateFormat($d) {
    switch (strlen("$d")) {
        case 4: return "'%Y'";
        case 6: return "'%Y%m'";
        case 8: return "'%Y%m%d'";
    }
}
// the date search string
$dateStr = "2004, 200501, 20050201, 20050701-20050731";

$dates = explode (',', $dateStr);
$where = array();
foreach ($dates as $d) {
    $d = trim($d);
    //is it a range
    if (strpos($d,'-')!==false) {
        list($d1, $d2) = explode ('-', $d);
        $d1 = trim($d1);
        $d2 = trim($d2);
        $f = myDateFormat($d1);
        $where[] = sprintf("(DATE_FORMAT(datecol, %s) BETWEEN '%d' AND '%d')", $f, $d1, $d2);
    }
    else  {
        $f = myDateFormat($d);
        $where[] = "(DATE_FORMAT(datecol, $f) = '$d')";
    }    

}
$wheredate = '(' . join (" \nOR ", $where) . ')';


echo '<pre>', $wheredate, '</pre>';
?>

-->

((DATE_FORMAT(datecol, '%Y') = '2004') 
OR (DATE_FORMAT(datecol, '%Y%m') = '200501') 
OR (DATE_FORMAT(datecol, '%Y%m%d') = '20050201') 
OR (DATE_FORMAT(datecol, '%Y%m%d') BETWEEN '20050701' AND '20050731'))

tcarnes

Thanks barand!

Your code helped me a great deal. I only had to tweak it a bit because I'm using MS SQL 2005.

Thanks again!

Terry