3

I'm querying dates from a mysql database. When I try to select only the dates between a certain from and to date, I'm not getting the expected results back.

My query:

select * from tbl_billing where date BETWEEN '09-02-2017' and '10-02-2017'

My output:

09-02-2017
10-02-2017
10-01-2017
09-01-2017
09-01-2017

My desired result:

09-02-2017
10-02-2017
Juliën
  • 9,047
  • 7
  • 49
  • 80
phpguru
  • 29
  • 1
  • 3

1 Answers1

1

You can use canonical sql date format 

select * from tbl_billing where date BETWEEN '2017-02-09' and '2017-02-10'

or convert properly using str_to_date 

select * from tbl_billing where date BETWEEN str_to_date('09-02-2017', '%d-%m-%Y')
            and  str_to_date('10-02-2017', '%d-%m-%Y')

but if also your date is a varchar then you must convert this column too 

select * from tbl_billing where str_to_date(date,'%d-%m-%Y')  BETWEEN str_to_date('09-02-2017', '%d-%m-%Y')
            and  str_to_date('10-02-2017', '%d-%m-%Y')
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • hello roman thanks by advice me but the issue was my date datatype is varchar and i cant change it because i used in many pages so it cant be possible to change datatype varchar to date. if you have any solution please tell me roman sir – phpguru Feb 18 '17 at 12:36
  • 1
    @phpguru Change it. Change the pages. Otherwise no 'guru'!! – Strawberry Feb 18 '17 at 13:29
  • @phpguru if you have also the date column as varchar then you must convert the column too .. answer updated ..let me know – ScaisEdge Feb 18 '17 at 15:37
  • hey Strawberry please learn php in deeply otherwise no Strawberry only lollypop, im getting that with my own login... – phpguru Feb 20 '17 at 05:54