Returning Rows from MySql less than 60 Days Old
I have the following query returning all rows from my table:
$query="SELECT * FROM $tbl_name ORDER BY job_id DESC";
I'd like to limit those results by entries less than 60 days old. I record the date an entry was made to the database using:
$dt=date('d M Y');
And this is stored in a column c开发者_StackOverflow社区alled 'date'.
Can someone help me to modify my query?
Thanks Dan
If date is stored like a varchar in database, your query should be:
SELECT *
FROM $tbl_name
WHERE TO_DATE(date, 'dd MON yyyy') >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC
if date is stored like a date, use:
SELECT *
FROM $tbl_name
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC
$query="SELECT * FROM $tbl_name WHERE DATEDIFF(CURDATE(), STR_TO_DATE(date,'%d %M %Y')) <60 ORDER BY job_id DESC"
SELECT * FROM $tbl_name FROM_UNIXTIME(date) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) ORDER BY job_id DESC
Try this (untested)
SELECT * FROM $tbl_name WHERE date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) ORDER BY job_id DESC
You can use as
select b.mobile_number, max(b.bill_number), max(b.created_on), c.name from MasterBill as b, MasterCustomer as c WHERE b.created_on < NOW() - INTERVAL 60 DAY and b.mobile_number = c.mobile_number group by mobile_number;
consider mobile number is key binding data with customer master and billing.
精彩评论