Jump to content


Photo

Interval of dates


  • Please log in to reply
4 replies to this topic

#1 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 05 June 2006 - 01:28 PM

Yo,

I need some help here.. I want to select a interval of dates based on now(), where it start 7 days ago and go to 14 days after.. I've tried to use a function to calculate start/end date, but unhappy its wont work too..

My printed sql is:
SELECT
    id , data_nascimento , nome
FROM
    tbShalom_Alunos
WHERE
        data_nascimento >= "22/05"
    AND
        data_nascimento <= "12/06"
ORDER BY
    SUBSTRING( data_nascimento , 7 , 2 ) ASC ,
    nome ASC
22/05 and 12/06 is a datee() php type, I can print it as I wish, 'd/m' is on..

Thanks for any help =)

D.Soul
(If something is wrong, please tell me. I'm learning this language. Thank you)

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 June 2006 - 01:51 PM

try using DATE_SUB() and DATE_ADD():
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM tableName WHERE date > DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND date < DATE_ADD(CURDATE(), INTERVAL 14 DAY);
[!--sql2--][/div][!--sql3--]

good luck!
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 05 June 2006 - 02:07 PM

Yo,

I've tried it and tried between, but it don't work well.. my data_nascimento field is birthday type, so, the year are "random"..

And I don't know how to do some like it:
YEAR( data_nascimento ) . "0522"[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
data_nascimento
FROM tbShalom_Alunos WHERE data_nascimento
BETWEEN
YEAR( data_nascimento ) . "0522" AND "20060612" [!--sql2--][/div][!--sql3--]
Thank you for the help =)

D.Soul
(If something is wrong, please tell me. I'm learning this language. Thank you)

#4 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 05 June 2006 - 02:52 PM

/** SOLVED **/
SELECT
    id , data_nascimento , nome
FROM
    tbShalom_Alunos
WHERE
        SUBSTRING( data_nascimento , 5 , 4 ) >= '. $arr_aniversariante[0] .'
    AND
        SUBSTRING( data_nascimento , 5 , 4 ) <= '. $arr_aniversariante[2] .' 
ORDER BY
    SUBSTRING( data_nascimento , 5 , 2 ) ASC ,
    SUBSTRING( data_nascimento , 7 , 2 ) ASC ,
    nome ASC
=D Thank you!

D.Soul
(If something is wrong, please tell me. I'm learning this language. Thank you)

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 June 2006 - 04:41 PM

Don't rely on SUBSTRING() -- it's an accident that MySQL dates are stored as strings. It's better to extract the month/date components and check them directly; a BETWEEN wouldn't hurt either.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users