CODEWITHSUNDEEP
phpmysql
Prince
Prince

Reputation: 1290

Mysql Delete between two dates

I want to delete details between two dates

My code is given below

Delete from emp_leave_details 
where emp_id='$id' 
and from_date BETWEEN '$f_date' AND '$t_date'

The problem is that it deleted all record between $f_date and $t_date but didn't delete the record of $t_date .I want to delete the records from $t_date also.

Upvotes: 0

Views: 16908

Answers (6)

Nadeem_MK
Nadeem_MK

Reputation: 7689

The problem is that for the $t_date, it starts at 00:00:00. That is the beginning of the day. Everything during the day is not included. If you want to include the records of $t_date, you should add 1 to it.

$t_date = $t_date + 1

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

Solution

Delete from emp_leave_details 
where emp_id='$id' 
and DATE(from_date) BETWEEN DATE('$f_date') AND DATE('$t_date')

Explanation

Yours query is equal to from_date >= '$f_date' AND from_date <= '$t_date', meaning it will ignore all that last day ('2013-09-05' < '2013-09-05 00:00:01'). Mine above gives ('2013-09-05' <= '2013-09-05') instead.

DATE() simply strips the values of hours, minutes and seconds and gives you expected result since BETWEEN uses <= and >=.

Upvotes: 4

avalkab
avalkab

Reputation: 436

mysql_query("Delete from emp_leave_details Where emp_id='$id' and (from_date BETWEEN '$f_date' AND '$t_date'");

Upvotes: 0

Ashwini Agarwal
Ashwini Agarwal

Reputation: 4858

Try this...

Delete from emp_leave_details 
  Where emp_id='$id' 
   AND from_data > '$f_date' 
   AND from_date <= '$t_date';

Upvotes: 2

mavrosxristoforos
mavrosxristoforos

Reputation: 3643

I can't see why you also include an id in your query, but answering to your question directly, you should

Delete from emp_leave_details
  Where emp_id='$id'
  and from_date > '$f_date'
  and from_date <= '$t_date'

Upvotes: 0

juergen d
juergen d

Reputation: 204766

When you have a datetime column then using the date will not suffice since 2013-09-25 12:00:00 is greater than 2013-09-25

You can you this

Delete from emp_leave_details 
Where emp_id = '$id' 
and from_date >= '$f_date' 
and from_date < '$t_date'

and set '$t_date' to 1 day later.

Upvotes: 1

Related Questions