mysql - what is wrong with this query?(sql convert date) -

the following query working :

select *  msuser  absensi_tanggal = date_format( str_to_date( "' .$query_array['datepicker'] . '", \'%m/%d/%y\' ) , \'%y-%m-%d\' ) 

however, query not working. should miss there :

select *  msuser  absensi_tanggal between date_format( str_to_date( "' .$query_array['datepicker1'] . '", \'%m/%d/%y\' ) , \'%y-%m-%d\' )  , date_format( str_to_date( "' .$query_array['datepicker2'] . '", \'%m/%d/%y\' ) , \'%y-%m-%d\' ) 

the query above return 0 result.

any appreciated, :d

since str_to_date returns proper date type value can used between, have tried simply:

select *  msuser  absensi_tanggal  between  str_to_date( "' .$query_array['datepicker1'] . '", \'%m/%d/%y\' ) ,  str_to_date( "' .$query_array['datepicker2'] . '", \'%m/%d/%y\' ) 

my guess mysql choking on converting front string date , string , again date make between work.

also, try make bit tidier:

" select *  msuser  absensi_tanggal  between  str_to_date('{$query_array['datepicker1']}','%m/%d/%y') ,  str_to_date('{$query_array['datepicker2']}','%m/%d/%y'); " 

and in case aren't confirming value of datepicker1 earlier datepicker2, can run query sure (though advise testing):

select *  msuser   ( absensi_tanggal  between  str_to_date('{$query_array['datepicker1']}','%m/%d/%y') ,  str_to_date('{$query_array['datepicker2']}','%m/%d/%y') ) or ( absensi_tanggal  between  str_to_date('{$query_array['datepicker2']}','%m/%d/%y') ,  str_to_date('{$query_array['datepicker1']}','%m/%d/%y') ); "