SQL time period query

Question

I need to select from a table all rows that have date_added between CURDATE() and 6 weeks ago.

Help please.

Answer 1

SELECT  *
FROM    mytable
WHERE   date_added BETWEEN CURDATE() - INTERVAL 6 WEEK AND CURDATE()

Answer 2

SELECT *
FROM a_table
WHERE date_added BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()

Answer 3

SELECT * FROM table_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_added) <= 42

Answer 4

SELECT * FROM table_name WHERE DATEDIFF(NOW(),date_added)<=42

Answer 5

if u use dates in one year

select date_format(date, '%u') from tab

where (date_format(date, '%u')-date_format(now(), '%u'))>6

if u use dates with dufferent years

U don't have to use dates with anoter year. u can use

select

date_format(date, '%u') from tab

where (date_format(date, '%u')-date_format(now(), '%u'))>6

and

date_format(date, '%u') from tab

where (date_format(date, '%Y')-date_format(now(), '%Y'))=0

u can optimize query with join if u want. I think u now how to do it