CODEWITHSUNDEEP
mysqldate
Itai Sagi
Itai Sagi

Reputation: 5615

Selecting records between two dates

I have the following query:

SELECT dm.app_id, apt.app_name, COUNT(dm.app_id) 
FROM dm_openapp dm
JOIN app_table apt ON dm.app_id = apt.app_id
GROUP BY dm.app_id

Basically this table also has dates associated to each record, and I need to get a range of all the records between time X and Y, I tried using the following, for example, but to no avail:

WHERE dm.dl_time BETWEEN '2011-05-31' AND '2011-05-06'

Any idea as to what to do? the dl_time column is a timestamp type.

Upvotes: 4

Views: 23268

Answers (4)

user12504414
user12504414

Reputation: 

SELECT date_column
FROM dates
WHERE date(date_column)<='2011-05-06' AND date(date_column)>='2011-05-31'

Upvotes: 1

Bohemian
Bohemian

Reputation: 425418

Ummm... you've got the data the wrong way around. BETWEEN must be LOW value to HIGH value:

Try this:

WHERE dm.dl_time BETWEEN '2011-05-06' AND '2011-05-31' -- Note date values swapped

You can ignore the other answers, which also haven't noticed this...

Upvotes: 6

maple_shaft
maple_shaft

Reputation: 10463

You need to convert the strings in your where clause to a date STR_TO_DATE. Here are a variety of different functions in MySQL that you can use for DATE manipulation.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Upvotes: 0

Erveron
Erveron

Reputation: 1928

It is better to use DATETIME column type for these things. Than this should work: use str_to_date() function. Also, swap the BETWEEN values.

WHERE dm.dl_time BETWEEN str_to_date('2011-05-06','%Y-%m-%d') AND str_to_date('2011-05-31','%Y-%m-%d')

Upvotes: 7

Related Questions