Don't return results from today

Databases
1

I want to ensure that I don’t send more than one email to my users per day, so I log a time stamp for their last email in a column called lastMatch. It uses a datetime like 2011-09-09 11:02:14

What is the best way to write a query that will only return a list of registered users where lastMatch is not same day? So if their last time stamp is on September 9th, I don’t want them to show up in the list of results unless that current date is September 10th. I don’t want to use time, rather, just the date. But the date I’m storing is a time stamp. Here is what I came up with, but it takes into account the time:

select uID, lastMatch from users where lastMatch <= DATE_SUB(CURDATE(), INTERVAL 1 DAY) order by lastMatch;

Thank!

2

well, for starters, it won’t work in mysql, it was written for microsoft sql server database

3

Yes, I did try yours and it worked great. That is why I was confused why this other posting would somehow be a better option.

4

did you try mine? and you didn’t like it, or what?

i thought it was simple and elegant :slight_smile:

5

gk53, can you explain your version a little bit more?

Thanks!

6

where datediff(day,getdate(),lastMatch)>0

:wink:

7

yes it is for MSSQL use function datediff in days between current date in system (sql FUNCTION) getdate() AND date in your database field. if datediff equal 0 the same day in > 0 in the past if < 0 in future

8

WHERE lastMatch < CURRENT_DATE

:slight_smile: