SQL query does not select all rows between dates
My SQL query does not select all the rows I requested. It only considers the daytime portion of the date and time; months do not pay attention.
This is my request:
SELECT *
FROM Reservations
WHERE ReservationDate >= '24/04/2015' AND ReservationDate <= '24/03/2015'"
There are no results for these dates, but when the dates are changed:
SELECT *
FROM Reservations
WHERE ReservationDate >= '24/04/2015' AND ReservationDate <= '17/03/2015'"
I get 2 results when ReservationDate= "17/04/2015 15:02:03" and "21/04/2015 16:05:56"
.
By the way, the ReservationDate field is in string format. These are the same results as in DateTime.
ReservationID FriendID TableNumber ReservationDate ReservationStatus
16 58767732 32 21/04/2015 17:06:54 False
17 -1 32 21/04/2015 17:10:41 False
18 -1 2 21/04/2015 17:17:23 False
2 58767732 3 04/04/2015 19:37:17 False
3 -1 7 04/04/2015 19:37:43 False
4 -1 5 04/04/2015 23:24:24 False
5 -1 31 05/04/2015 16:29:02 False
6 -1 6 05/04/2015 16:40:29 False 7 -1 6 05/04/2015 17:12:47 False
8 58767732 32 09/04/2015 16:24:00 False
9 -1 6 09/04/2015 16:25:03 False
source to share
Try using the operator between dates
select * from Reservations
where
ReservationDate between '17/03/2015'
and
DATE_ADD('24/04/2015',INTERVAL 1 DAY) // or something like that
Also, in MySQL, you can use the DATE function to extract a date from a date and time:
select * from Reservations
where
DATE(ReservationDate) BETWEEN '17/03/2015' AND '24/03/2015'
If you are using MS Sql Server there are some other workarounds to get date from string.
UPDATE:
Since @Netanelgo said he uses MS Access:
Try CDate()
converting your string to date.
select * from Reservations
where CDate(date) between #17/03/2015# and #24/03/2015#;
If that doesn't work because CDate won't reconfigure your format, you can use DateSerial (year, month, day) to plot the date. You will need to use mid $ and Cint () to create the year, month and day arguments. Something like this for the "yyyy-mm-dd" format:
DateSerial(CInt(mid(date, 1, 4)), CInt(mid(date, 6, 2)), CInt(mid(date, 9, 2))
source to share
I tried to recreate this but didn't get any rows of data when queried with these dates. This may be because (as Abbaslem emphasizes) and> seems a little confusing.
The problem is that you are storing dates as a string. As Bojan mentions, you can get around this in MySQL with the DATE () statement. In T-SQL, you can use a listing like below:
set dateformat dmy
create table #reservations (ReservationID int, FriendID int, TableNumber int, ReservationDate varchar(30), ReservationStatus bit)
INSERT INTO #Reservations values (16, 58767732, 32, '21/04/2015 17:06:54', 'FALSE'),
(17, -1, 32, '21/04/2015 17:10:41', 'FALSE'),
(18, -1, 2, '21/04/2015 17:17:23', 'FALSE'),
(2, 58767732, 3, '04/04/2015 19:37:17', 'FALSE'),
(3, -1, 7, '04/04/2015 19:37:43', 'FALSE'),
(4, -1, 5, '04/04/2015 23:24:24', 'FALSE'),
(5, -1, 31, '05/04/2015 16:29:02', 'FALSE'),
(6, -1, 6, '05/04/2015 16:40:29', 'FALSE'),
(8, 58767732, 32, '09/04/2015 16:24:00', 'FALSE'),
(9, -1, 6, '09/04/2015 16:25:03', 'FALSE'),
(7, -1, 6, '05/04/2015 17:12:47', 'FALSE')
SELECT *
FROM #Reservations
WHERE cast(left(ReservationDate,10) as date) <= '24/04/2015' AND cast(left(ReservationDate,10) as date) >= '17/03/2015'
drop table #reservations
and when i changed the dates it only returned the reservations i expected to see.
source to share