CODEWITHSUNDEEP
mysqldateselect
Jason Sweet
Jason Sweet

Reputation: 1695

MySQL Query - Records between Today and Last 30 Days

I want to return all records that were added to the database within the last 30 days. I need to convert the date to mm/dd/yy because of display purposes. 

create_date between DATE_FORMAT(curdate(),'%m/%d/%Y') AND (DATE_FORMAT(curdate() - interval 30 day,'%m/%d/%Y'))

My statement fails to limit the records to the last 30 days - it selects all the records.

Can anyone point me in the right direction? It feels like I am close.

Thanks and have a great week.

Upvotes: 167

Views: 353214

Answers (7)

Bret Weinraub
Bret Weinraub

Reputation: 2283

Here is a formulation not shown in the other answers, showing that the last 30 days worth of data can be returned without the "BETWEEN" operator and/or DATE_ADD()/DATE_SUB() functions:

SELECT DATE_FORMAT(create_date, '%m/%d/%y') 
FROM table
WHERE create_date > (curdate() - interval 30 day);

Note if you are storing just the date, use curdate(), if you have a timestamp, use now().

See Quassnoi's answer for a description of why that is.

Upvotes: 4

Rohit Suthar
Rohit Suthar

Reputation: 3628

You can also write this in mysql - 

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date < DATE_ADD(NOW(), INTERVAL -1 MONTH);

FIXED

Upvotes: 17

mvirant
mvirant

Reputation: 21

Here's a solution without using curdate() function, this is a solution for those who use TSQL I guess

SELECT myDate
FROM myTable
WHERE myDate BETWEEN DATEADD(DAY, -30, GETDATE()) AND GETDATE()

Upvotes: 2

Thinkcast
Thinkcast

Reputation: 731

SELECT
    *
FROM
    < table_name >
WHERE
    < date_field > BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)
AND NOW();

Upvotes: 73

Quassnoi
Quassnoi

Reputation: 425251

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()

Upvotes: 378

onlybymyexerperience
onlybymyexerperience

Reputation: 91

For the current date activity and complete activity for previous 30 days use this, since the SYSDATE is variable in a day the previous 30th day will not have the whole data for that day.

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

Upvotes: 9

Rich Adams
Rich Adams

Reputation: 26574

DATE_FORMAT returns a string, so you're using two strings in your BETWEEN clause, which isn't going to work as you expect.

Instead, convert the date to your format in the SELECT and do the BETWEEN for the actual dates. For example,

SELECT DATE_FORMAT(create_date, '%m/%d/%y') as create_date_formatted
FROM table
WHERE create_date BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE()

Upvotes: 15

Related Questions