query between two dates using SQL?

Question

I have a table which is clientbike

SELECT stime,etime FROM clientbike

type = datetime

Result

stime               | etime 
-------------------------------------------- 
2016-04-18 00:00:00 | 2016-05-18 00:00:00
--------------------------------------------
2016-04-05 00:00:00 | 2016-06-05 00:00:00
--------------------------------------------
2016-04-03 00:00:00 | 2016-07-03 00:00:00

Now I want to search function:

all query result found 0 but i want 3 ...

Search Query Using >= AND <=

   SELECT stime,etime FROM clientbike WHERE 
   stime >= '2016-04-25 00:00:00' AND 
   etime <= '2016-04-26 00:00:00'

Wrong

   No Result Found...

search query using between

   SELECT stime,etime FROM clientbike WHERE 
   stime BETWEEN '2016-04-25 00:00:00' AND '2016-04-26 00:00:00' AND 
   etime BETWEEN '2016-04-25 00:00:00' AND '2016-04-26 00:00:00'

Wrong

   No Result Found...

search query using between with str_to_date

   SELECT stime,etime FROM clientbike WHERE 
   STR_TO_DATE(stime, '%Y-%m-%d') BETWEEN '2016-04-25 00:00:00' AND '2016-04-26 00:00:00' AND 
   STR_TO_DATE(etime, '%Y-%m-%d') BETWEEN '2016-04-25 00:00:00' AND '2016-04-26 00:00:00'

Wrong

   No Result Found...

Can someone explain this behavior?

Be very careful about writing dates `d-m-Y`. Depending on where you live it means different things. Write dates `Y-m-d`. — Scopey, Apr 17 '16 at 06:29
In every day life I use d-m-Y, but in programming I use Y-m-d so there's absolutely no ambiguity. I would highly recommend that you do it too. — Scopey, Apr 17 '16 at 06:33
No result because you have no Record is between those dates !!! :) — Scooter Daraf, Apr 17 '16 at 09:02
i want to between records result .. example stime -> 6 & etime-> 9 and user search stime ->7 & etime-> 8 then result found stime -> 6 & etime-> 9 — sanjay joon, Apr 17 '16 at 09:05

score 2 · Answer 1 · answered Apr 17 '16 at 06:31

2

stime      | etime 
------------------------ 
15-04-2016 | 31-06-2016

That format means your fields are varchars and not date hence you can not compare them ordinarily like dates can be compared.

If you were using proper date type field, format would have been YYYY-MM-DD and then any standard comparison would work just fine. Since that is not the case you have to convert these strings to dates to compare them correctly.

Best course of action is to change your fields to use correct data type for this purpose, and that is DATE

answered Apr 17 '16 at 06:31

Hanky Panky

46,730
8
72
95

but , i use only d-m-Y – sanjay joon Apr 17 '16 at 06:36
if i use date value in mysql my date format auto change and show ' YYYY-MM-DD '... – sanjay joon Apr 17 '16 at 06:37
You can easily convert the display date to any format that you like – Hanky Panky Apr 17 '16 at 06:43
Like this http://stackoverflow.com/questions/2167916/convert-one-date-format-into-another-in-php – Hanky Panky Apr 17 '16 at 06:51

Scooter Daraf · Answer 2 · 2016-04-17T09:05:15.217

0

you have to convert your columns to be as dates

   STR_TO_DATE(stime, '%Y-%m-%d')

Please try this

   SELECT stime,etime FROM clientbike 
   WHERE STR_TO_DATE(stime, '%Y-%m-%d') BETWEEN '2016-04-01' AND '2016-04-17'
     AND STR_TO_DATE(etime, '%Y-%m-%d') BETWEEN '2016-05-01' AND '2016-07-26'

or

in your database change that column from varchar to date or datetime

EDIT

sqlfiddle example

edited Apr 17 '16 at 09:05

answered Apr 17 '16 at 06:35

Scooter Daraf

525
7
23

Yep good! I think OP will go this way cause they dont seem to be eager to make changes to their existing structure – Hanky Panky Apr 17 '16 at 07:08
Try use this updated dates and you will get results . you have no result because the dates are over you searching dates , and no value matches – Scooter Daraf Apr 17 '16 at 09:06
Check sqlfiddle example – Scooter Daraf Apr 17 '16 at 09:09
i want to between records result .. example stime -> 6 & etime-> 9 and user search stime ->7 & etime-> 8 then result found stime -> 6 & etime-> 9 – sanjay joon Apr 17 '16 at 09:13

Piyush Gupta · Answer 3 · 2016-04-18T04:14:34.720

First thing I can see in your question your stime and etime column is string type so change data type to date in database and for debugging you can check below query.

You need to just copy below query and execute in mysql, then you can see this query is returning number of count entry from staring date to end date.

You can achieve this in two way,
Option 1

    SET @FromDate := '2016-04-1'  
    SET @ToDate := '2016-04-30' 

    SELECT COUNT(* ) FROM clientbike
    WHERE stime >= @FromDate AND etime <= @ToDate

This is covering all date range scenario like selected @FromDate and @ToDate both are within start and end column in table.

Option 2

     SET @FromDate := '2016-04-1'  
     SET @ToDate := '2016-04-30' 
     SELECT count(*) FROM clientbike WHERE stime BETWEEN @FromDate AND @ToDate AND etime BETWEEN @FromDate AND @ToDate

query between two dates using SQL?

3 Answers3