MySQL Query – Records between Today and Last 30 Days

by

You need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today’s records.

In this case, you’ll need to use NOW instead:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

More Related Contents:

  1. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  2. Select mysql query between date?
  3. How to get an age from a D.O.B field in MySQL?
  4. How do I get the first day of the week of a date in mysql?
  5. Compare only day and month with date field in mysql
  6. Recreating a query using INNER JOIN? [duplicate]
  7. MySQL – UPDATE query based on SELECT Query
  8. How to convert a string to date in MySQL?
  9. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  10. Converting a date in MySQL from string field
  11. MySQL: Select All Dates In a Range Even If No Records Present
  12. CURRENT_DATE/CURDATE() not working as default DATE value
  13. INSERT INTO…SELECT for all MySQL columns
  14. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  15. Get the latest date from grouped MySQL data
  16. MySQL difference between two rows of a SELECT Statement
  17. MySQL – How to search for exact word match using LIKE?
  18. Count days between two dates, excluding weekends (MySQL only)
  19. How to select the most recent set of dated records from a mysql table
  20. MySQL date format
  21. quick selection of a random row from a large table in mysql
  22. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  23. mysql select sum group by date
  24. SELECT query return 1 row from each group
  25. Comma separated string of selected values in MySQL
  26. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  27. MySQL Error: #1142 – SELECT command denied to user
  28. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  29. MySQL cumulative sum grouped by date
  30. MySQL cartesian product between two SELECT statements

Leave a Comment