Hey gurus,
I would like to know how to make a query with time involved, online searches are just confusing me and im not getting far. The objective is to create a query which finds all records 7 days old. The dates are stored in the database like "2010-01-31".
So the query would probably have to use curdate() then + 7 days or more, i dunno.
Can anyone give me an example? I appreciate it.
Regards
Tony
SQL query with date? little help [SOLVED]
Moderator: General Moderators
SQL query with date? little help [SOLVED]
Last edited by synical21 on Wed Feb 03, 2010 1:16 pm, edited 1 time in total.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: SQL query with date? little help
Im guessing you want records 7 days older than the current day. Here's how I would start;So the query would probably have to use curdate() then + 7 days or more, i dunno.
Code: Select all
<?php
$sevenDayVariable = date('Y-m-d', time()-604800);
$query = mysql_query("SELECT * FROM table WHERE datefield = curdate() AND datefield >= '".$sevenDayVariable."' ");
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: SQL query with date? little help
This should work better :
Code: Select all
<?php $sevenDayVariable = date('Y-m-d', time()-604800);
$query = mysql_query("SELECT * FROM dates WHERE date <= CURDATE() AND date >= '".$sevenDayVariable."' "); ?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: SQL query with date? little help
Keep it simple & do it all in the query with DATE_SUB
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: SQL query with date? little help
Thanks for the replied, i will try use them now. 
Re: SQL query with date? little help
Thanks for the help i have done it now 
For those who need it.
Code: Select all
SELECT * FROM table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 7 DAY ) AND CURDATE( )