Searching Using Two Dates

[Anon]

Greetings (eh),

I'm trying to search a payroll database. When I search using one date , i.e. all employee hours for a particular day, I have no problems.

I'd also like the user to enter two dates (start of the work week, month; end of the work week, month) and then display all employee hours. When I execute the code I get SQl errors.

Could someone please educate me on the intricacies of using dates in select statements.

//field job_date is stored in the table
//it's type is date in yyyy-mm-dd format
//example of a table entry is 2000-12-28

//===========================================================
//=========SEARCHING WITH ONE DATE ==========================
//=========THIS CODE WORKS, SEARCH PERFORMED CORRECTLY ======
//===========================================================
//build date search string
$date = "";
$date .= $job_year;
$date .= "-";
$date .= $job_month;
$date .= "-";
$date .= $job_day;

// Generate the SQL command for doing a select from the Database
$searchStmt = "SELECT * from $tableName where " ;

$searchStmt .= "job_date like '%$date%' and " ;

$stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ;

//===========================================================
//=========SEARCHING WITH TWO DATES==========================
//=========THIS CODE PRODUCES SQL THE FOLLOWING ERROR MSG ===
//===========================================================
Error in executing SELECT * from tbl_labour where
job_date => '%2001-01-31%'and job_date <= '%2001-02-01%' stmt
error:1064 You have an error in your SQL syntax
near '=> '%2001-01-31%' and job_date <= '%2001-02-01%' ' at line 1
//===========================================================
/build first date search string
$date1 = "";
$date1 .= $job_year1;
$date1 .= "-";
$date1 .= $job_month1;
$date1 .= "-";
$date1 .= $job_day1;

//build second date search string
$date2 = "";
$date2 .= $job_year2;
$date2 .= "-";
$date2 .= $job_month2;
$date2 .= "-";
$date2 .= $job_day2;

// Generate the SQL command for doing a select from the Database
$searchStmt = "SELECT * from $tableName where " ;

$searchStmt .= "job_date => '%$date1%' and " ;

$searchStmt .= "job_date <= '%$date2%' and " ;

$stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ;

[brandonschnell]

SELECT * from tbl_labour where job_date >= '%2001-01-31%'and job_date <= '%2001-02-01%'

remember, its either greater than or equal to. not equal to or greater than.

this method may not search like you might expect it to. i'd be curious if it worked.

[ateng]

If I'm not mistaken ,try to use mysql function date_format..
you can't compare two dates as strings..

[Anon]

I've coded the same thing yesterday.
You need to remove the % signs. Also, are the columns you are using DATE type?

Example:
SELECT * FROM payroll WHERE payday >= '1998-02-03' AND payday <= '1999-11-10';

ateng wrote:

If I'm not mistaken ,try to use mysql function date_format..
you can't compare two dates as strings..