Re: Selecting by date
I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? Assuming the field date is of type datetime: WHERE (TO_DAYS(CURDATE()) - TO_DAYS(date)) = 1 HTH -- Lowell Allen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting by date
From: Matthew Stuart [mailto:[EMAIL PROTECTED] I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? You probably want something like this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW() - INTERVAL 1 DAY; Also note that if you're looking specifically at a date, you might want to use CURDATE() instead of NOW(), as NOW() returns the full -mm-dd hh:ii:ss stamp (whereas CURDATE() returns just -mm-dd). MySQL handles it silently just fine, but it's a good habit to get into. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a date
Bruce Therrien wrote: Using a select statement, how does one select all records that have a date one day less than the current date? Our database has these fields: EMAIL, AMOUNT, ID, DATE The date is in format 00/00/ Gack. I'm guessing 00/00/ is a string? 1) DATE is a MySQL reserved word, a data type, meaning a date in -MM-DD format. 2) MySQL has date functions, but they require a real date field to operate on :-) So: CREATE myTable ( myDate DATE ); Then you can simply SELECT myDate FROM myTable WHERE myDate = DATE_SUB(NOW(), INTERVAL '1' DAY); Refs: http://www.mysql.com/doc/en/Date_and_time_types.html http://www.mysql.com/doc/en/Date_and_time_functions.html HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting a date
At 11:02 -0400 9/27/03, Bruce Therrien wrote: Using a select statement, how does one select all records that have a date one day less than the current date? Searching the archives and I can't seem to find the answer.. Our database has these fields: EMAIL, AMOUNT, ID, DATE The date is in format 00/00/ If your dates were stored in a DATE column, you could write your query like this: ... WHERE DATE = DATE_SUB(CURDATE(),INTERVAL 1 DAY) However, if your dates are in 00/00/, then I suppose they are stored in a string column. In this case, you'll have to figure out what yesterday's date is, then convert it to a string in the same format your dates are stored so that you can perform the comparison. Is there some reason you're not storing your dates in a DATE column? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
