vandersk
asked on
Using Between to select a date range does not retrieve inclusive dates.
Hi there
I always thought that “BETWEEN” Select * from table where SEARCH_DATE between '2005-12-01' AND '2005-12-31');
would select rows from say "2005-12-01 00:00:00" to "2005-12-31 23:59:59", seems like MYSQL does not.
I have a table. . .
ID|Search_Date_Time
3878879|2005-12-01 00:00:14
3878880|2005-12-01 00:00:15
. . .
3900061|2005-12-31 23:59:58
3900062|2005-12-31 23:59:58
When I do a select
SELECT ID, SEARCH_DATE_Time
FROM table1
WHERE
SEARCH_DATE between '2005-12-01' AND '2005-12-31');
. . .I only get rows up until '2005-12-30 23:59:59'
If I do
SELECT ID, SEARCH_DATE_Time
FROM table1
WHERE
SEARCH_DATE between '2005-12-01' AND '2005-12-31 23:59:59');
I do get the exact result
Is this to be expected?
How can I format the input date to search up to 23:59:59?
kind regards,
I always thought that “BETWEEN” Select * from table where SEARCH_DATE between '2005-12-01' AND '2005-12-31');
would select rows from say "2005-12-01 00:00:00" to "2005-12-31 23:59:59", seems like MYSQL does not.
I have a table. . .
ID|Search_Date_Time
3878879|2005-12-01 00:00:14
3878880|2005-12-01 00:00:15
. . .
3900061|2005-12-31 23:59:58
3900062|2005-12-31 23:59:58
When I do a select
SELECT ID, SEARCH_DATE_Time
FROM table1
WHERE
SEARCH_DATE between '2005-12-01' AND '2005-12-31');
. . .I only get rows up until '2005-12-30 23:59:59'
If I do
SELECT ID, SEARCH_DATE_Time
FROM table1
WHERE
SEARCH_DATE between '2005-12-01' AND '2005-12-31 23:59:59');
I do get the exact result
Is this to be expected?
How can I format the input date to search up to 23:59:59?
kind regards,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I do not understand why this answer merits a C - if you required more information - why didnt you ask for it?
I think that specifyig only '2005-12-31'. mysql will assume a time value of 0:00:00
Alternatively, You could say this
SEARCH_DATE between '2005-12-01' AND '2006-01-01');