SQL query doesn't select all rows between dates

Question

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

@Netanelgo please change `AND` to `OR`. You can't have reservations that have their date between two disjoint data ranges. — Kamil Gosciminski, Apr 24 '15 at 08:36
First and most important question is, are those really dates or rather strings? And which DBMS are you using? — fancyPants, Apr 24 '15 at 08:46
and yes, i'm doing a c# winform project, and selecting with DateTime.ToShortDateString method — Netanelgo, Apr 24 '15 at 08:50
What is the DBMS? And `'24/04/2015'` is a string, not a date. — Lalit Kumar B, Apr 24 '15 at 08:52
If some date is greater than/equal to `24/04/2015` then it CANNOT be less than `24/03/2015` at the same time. — Salman A, Apr 24 '15 at 10:16

score 4 · Answer 1 · edited May 23 '17 at 11:58

4

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

edited May 23 '17 at 11:58

Community

1
1

answered Apr 24 '15 at 08:41

TheBoyan

6,802
3
45
61

@Netanelgo What db are you using? Ms Sql Server, Oracle, MySql? – TheBoyan Apr 24 '15 at 09:03
1

@Netanelgo Have you tried this then: http://stackoverflow.com/questions/24622282/select-from-ms-access-table-between-two-dates – TheBoyan Apr 24 '15 at 09:13
It works! just a more minor problem, when it gets to dates as 12/03/2015 it selects dates in the format mm/dd/yyyy (when main is dd/mm/yyyy). what should I do? – Netanelgo Apr 24 '15 at 09:24
@Netanelgo I think that is probably your settings on your comp, regional settings etc. – TheBoyan Apr 24 '15 at 09:25
my comp uses dd/mm/yyyy. isn't there any programaticly way? – Netanelgo Apr 24 '15 at 09:28
@Netanelgo I think if you look around SO you will find pretty decent answers to how to convert to proper format. I found this one, but there may be others: http://stackoverflow.com/questions/17361338/convert-string-to-date-in-ms-access-query – TheBoyan Apr 24 '15 at 09:39

score 1 · Answer 2 · answered Apr 24 '15 at 08:38

1

On your first fist sentence change comparisons like below

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

answered Apr 24 '15 at 08:38

Abbas Elmas

422
6
16

`'24/04/2015'` is a string, and not a date. – Lalit Kumar B Apr 24 '15 at 08:51
1

You mean there is no difference a date and a literal? Then what's the purpose of data type conversion? If you compare a string with a date, then optimizer will do an implicit data type conversion, and you should avoid that. Always, explicitly convert the literal into date while comparing with a date. – Lalit Kumar B Apr 24 '15 at 11:04
i am not saying that, person ask this question tried with date format already thats not the fault. Of course it should be datetime, no need to discuss on type it should be datetime – Abbas Elmas Apr 24 '15 at 19:37
Alright mate. If you are aware of what I am trying to tell you, then we both are happy :-) – Lalit Kumar B Apr 24 '15 at 19:54

score 1 · Answer 3 · answered Apr 24 '15 at 09:02

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.

score 1 · Answer 4 · answered Apr 24 '15 at 09:53

1

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#;

answered Apr 24 '15 at 09:53

Gustav

53,498
7
29
55

SQL query doesn't select all rows between dates

4 Answers4