Jump to content

Archived

This topic is now archived and is closed to further replies.

Darkness Soul

Interval of dates

Recommended Posts

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:[code]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[/code]
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

Share this post


Link to post
Share on other sites
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] * [color=green]FROM[/color] [color=orange]tableName[/color] [color=green]WHERE[/color] date [color=orange]>[/color] DATE_SUB(CURDATE(), INTERVAL 7 DAY) [color=blue]AND[/color] date [color=orange]<[/color] DATE_ADD(CURDATE(), INTERVAL 14 DAY);
[!--sql2--][/div][!--sql3--]

good luck!

Share this post


Link to post
Share on other sites
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:
[b]YEAR( data_nascimento ) . "0522"[/b][!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
data_nascimento
[color=green]FROM[/color] [color=orange]tbShalom_Alunos[/color] [color=green]WHERE[/color] data_nascimento
BETWEEN
YEAR( data_nascimento ) . [color=red]"0522"[/color] [color=blue]AND[/color] [color=red]"20060612"[/color] [!--sql2--][/div][!--sql3--]
Thank you for the help =)

D.Soul

Share this post


Link to post
Share on other sites
/** SOLVED **/
[code]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[/code]
=D Thank you!

D.Soul

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.