CODEWITHSUNDEEP
sqldatetimems-access
Netanelgo
Netanelgo

Reputation: 49

SQL query doesn't select all rows between dates

My SQL query doesn't select all the rows I asked for. It only considers the Day part of the datetime; months don't get any attention.

This is my query:

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 to:

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, ReservationDate field is on string format. It's the same results even when it's on 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

Upvotes: 3

Views: 1088

Answers (4)

Gustav
Gustav

Reputation: 55806

This should do:

Select * From Reservations
Where 
    DateSerial(Mid(ReservationDate, 7, 4), Mid(ReservationDate, 4, 2), Mid(ReservationDate, 1, 2)) 
    Between #17/03/2015# and #24/03/2015#;

Upvotes: 1

TehBoyan
TehBoyan

Reputation: 6890

Try using the between operator for 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 the date from a datetime:

    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 the date from a string.

UPDATE:

Since @Netanelgo said he is using MS Access:

Try CDate() to convert your string into a date.

select  *  from Reservations
where CDate(date) between #17/03/2015# and #24/03/2015#;

If it doesn't work because CDate does not reconize your format you can use DateSerial(year, month, day) to build a Date. You will need to use mid$ and Cint() to build the year, month and day arguments. Something like this for a format "yyyy-mm-dd": 

DateSerial(CInt(mid(date, 1, 4)), CInt(mid(date, 6, 2)), CInt(mid(date, 9, 2))

Upvotes: 4

Zelazny
Zelazny

Reputation: 11

I tried recreating this, but didn't receive any rows of data when querying with those dates. This may be because (as abbaselmas points out) your < and > seem to be a little confused.

The issue seems to be that you're storing dates as a string. As The Bojan mentions, you can get around this in MySQL with the DATE() operator. In T-SQL, you can use cast, as follows:

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 was expecting to see.

Upvotes: 1

Abbas Elmas
Abbas Elmas

Reputation: 430

On your first fist sentence change comparisons like below

SELECT * 
FROM Reservations 
WHERE ReservationDate <= '24/04/2015' AND ReservationDate >= '24/03/2015'"

Upvotes: 1

Related Questions