Page 1 of 1

SQL query with date? little help [SOLVED]

Posted: Wed Feb 03, 2010 7:52 am
by synical21
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

Re: SQL query with date? little help

Posted: Wed Feb 03, 2010 9:52 am
by social_experiment
So the query would probably have to use curdate() then + 7 days or more, i dunno.
Im guessing you want records 7 days older than the current day. Here's how I would start;

Code: Select all

<?php 
 $sevenDayVariable = date('Y-m-d', time()-604800);
 $query = mysql_query("SELECT * FROM table WHERE datefield = curdate() AND datefield >= '".$sevenDayVariable."' ");
 ?> 
 

Re: SQL query with date? little help

Posted: Wed Feb 03, 2010 10:08 am
by social_experiment
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."' "); ?>

Re: SQL query with date? little help

Posted: Wed Feb 03, 2010 10:31 am
by pickle
Keep it simple & do it all in the query with DATE_SUB

Re: SQL query with date? little help

Posted: Wed Feb 03, 2010 12:06 pm
by synical21
Thanks for the replied, i will try use them now. :)

Re: SQL query with date? little help

Posted: Wed Feb 03, 2010 1:15 pm
by synical21
Thanks for the help i have done it now :D

Code: Select all

 
SELECT * FROM table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 7 DAY ) AND CURDATE( )
 
For those who need it.