0

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?

Improve this question
9
  • 1
    Be very careful about writing dates d-m-Y. Depending on where you live it means different things. Write dates Y-m-d. Commented Apr 17, 2016 at 6:29
  • yes , but date copy for exit data... Commented Apr 17, 2016 at 6:31
  • i don't use y-m-d , i use only d-m-Y Commented Apr 17, 2016 at 6:32
  • 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. Commented Apr 17, 2016 at 6:33
  • 1
    There is no 31 day in June and July 2016. Commented Apr 17, 2016 at 6:35

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

but , i use only d-m-Y
if i use date value in mysql my date format auto change and show ' YYYY-MM-DD '...
You can easily convert the display date to any format that you like
Like this stackoverflow.com/questions/2167916/…
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

Improve this answer

4 Comments

Yep good! I think OP will go this way cause they dont seem to be eager to make changes to their existing structure
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
Check sqlfiddle example
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
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.