Query using Date

Mark_Nordstrom

What I wanna do is update a field in a row based on the date.

UPDATE tabe SET field=field-field*variable WHERE date

What i cant figure out how to do is count days if date >=7 AND date <=13 Variable =.10 also keep going with it IF date >=14 AND date <=20 Variable =.20

What i cant figure out is how to count how many days away date is from the curdate

so if Date=2012-04-20 I wanna know how to count how many days that is from curdate

Now im pretty sure I can figure out how to do this if someone can help me in counting days date is from today.

Derokorian

Mark Nordstrom;11004479 wrote:
What i cant figure out how to do is count days if date >=7 AND date <=13 Variable =.10 also keep going with it IF date >=14 AND date <=20 Variable =.20

Sounds like you could use MySQL's CASE statement.

Mark Nordstrom;11004479 wrote:
What i cant figure out is how to count how many days away date is from the curdate

so if Date=2012-04-20 I wanna know how to count how many days that is from curdate

Sounds like you would want the DATEDIFF function here.

Lord_Yggdrasill

umm this should be easy, isnt it? First of all, initiate DateTime objects for curdate and the other date. Assume the olddate to compare with curdate is 04/30/2012, the code below will do the trick:

$curdate = new DateTime();
$olddate = new DateTime('2012-04-30');
$days = $curdate->diff($olddate);
echo "The old date is {$days->format('%R%a days')} away from current date";

Since it only calculates how many days away from the current date, the timezone may not be important. If you want to make a script that computes how many hours away from a specified time, you may want to specify timezone.

Lord_Yggdrasill

bradgrafelman;11004490 wrote:
@ Yggdrasill: Your example assumes that the data and/or difference calculation is being done in PHP. The OP is using SQL, not PHP.

Oh really? o_o I assume every thread in this subforum has something to do with PHP. If not, it should belong to the database subforum instead? 🙂

Lord_Yggdrasill

bradgrafelman;11004492 wrote:
waves hand But this is the Database forum you're looking for...

Last time I checked it was in the coding subforum. XD Nvm then, people make mistakes at times. I did it too when I was a newbie at this forum.

Mark_Nordstrom

Was actually for either. I can use php to get the data i want then do what i need with it. Thanks btw.

vivek151189

Mark Nordstrom;11004479 wrote:
What I wanna do is update a field in a row based on the date.

UPDATE tabe SET field=field-field*variable WHERE date

What i cant figure out how to do is count days if date >=7 AND date <=13 Variable =.10 also keep going with it IF date >=14 AND date <=20 Variable =.20

What i cant figure out is how to count how many days away date is from the curdate

so if Date=2012-04-20 I wanna know how to count how many days that is from curdate

Now im pretty sure I can figure out how to do this if someone can help me in counting days date is from today.

for applying thecondition u can also use between clause

Mark_Nordstrom

Yah I ended up using

DATE_SUB(CURDATE(),INTERVAL 14 DAY) > date AND DATE_SUB(CURDATE(),INTERVAL 20 DAY) < date