/    Sign up×
Community /Pin to ProfileBookmark

date – mysql help

Copy linkTweet thisAlerts:
Sep 13.2004

I have a date in a mysql database. (date datatype)
What would be my sql to select all records that the date is less than one week from todays date?

I tried but it is not giving the correct results:
select * from items where TO_DAYS(NOW()) – TO_DAYS(datetoremove) <= 7;

to post a comment
PHP

9 Comments(s) ↴

Copy linkTweet thisAlerts:
@AdamGundrySep 13.2004 — You can probably use this (untested):

SELECT * FROM items WHERE DATE_SUB(CURDATE(), INTERVAL 1 WEEK) < datetoremove;

Adam
Copy linkTweet thisAlerts:
@estheraauthorSep 14.2004 — MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WEEK ) < datetoremove LIMIT 0, 30' at line 3
Copy linkTweet thisAlerts:
@AdamGundrySep 14.2004 — Try "7 DAY" instead of "1 WEEK" - I think the latter might not be supported in some MySQL versions. If that doesn't work, can you post the whole query you are using?

Adam
Copy linkTweet thisAlerts:
@estheraauthorSep 14.2004 — SELECT *

FROM items

WHERE DATE_SUB( CURDATE( ) , INTERVAL 7

DAY ) < datetoremove

This is still returning dates in december. It should return anything with a date less than september 21 (7 days from today)

What am I doing wrong?
Copy linkTweet thisAlerts:
@NogDogSep 14.2004 — How about this:
<i>
</i>SELECT * FROM items WHERE DATEDIFF(CURDATE(), datetoremove) &lt;= 7;
Copy linkTweet thisAlerts:
@estheraauthorSep 14.2004 — still doesn't work

SELECT * FROM items WHERE DATEDIFF(CURDATE(), datetoremove) <= 7;
Copy linkTweet thisAlerts:
@NogDogSep 14.2004 — [i]Originally posted by esthera [/i]

[B]still doesn't work



SELECT * FROM items WHERE DATEDIFF(CURDATE(), datetoremove) <= 7; [/B][/QUOTE]

OK, I [i]think[/i] I got the sequence backwards. Try this:

SELECT * FROM items WHERE DATEDIFF(datetoremove, CURDATE()) &lt;= 7;
Copy linkTweet thisAlerts:
@estheraauthorSep 15.2004 — SQL-query :

SELECT *

FROM items

WHERE DATEDIFF(datetoremove, CURDATE()) <=7;


MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( datetoremove, CURDATE( ) ) <=7 LIMIT 0, 30' at line 3
Copy linkTweet thisAlerts:
@ShrineDesignsSep 15.2004 — try this:SELECT * FROM <span><code>items</code></span> WHERE <span><code>datetoremove</code></span> &gt;= SUB_DATE(NOW(), INTERVAL 7 DAYS)
×

Success!

Help @esthera spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.18,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...