Date Range Query not returning all records - SQL -

i have sql db need filter results based on month inventory items completed in order billing.

the field i'm working called completiondate. here code i'm using.

input name='criteria' type='hidden' value="where completiondate between '8-1-2013' , '8-31-2013'" 

i of records returned not all. suspect problem lies in format of completiondate field. varchar(10). storing data in field in format mm-dd-yyyy.

after searching understand since field in varchar datatype above mentioned code not going work way want to. i've tried no avail.

input name='criteria' type='hidden' value="where to_date(completiondate, 'mm-dd-yyyy') between to_date('8-1-2013', 'mm-dd-yyyy') , to_date('8-31-2013', 'mm-dd-yyyy')" 

can guide me solution?

because field of type varchar, not searching date range text range. should store dates in date type field, because can , efficiently date range queries.

your workaround using to_date should in theory work, although rather inefficient database has try convert text each row real date in order able comparison. why not work still unclear. database using? format string correct or should in uppercase ('mm-dd-yyyy')?

if stubborn in storing dates text in database, using format can lexically sorted, e.g. yyyy-mm-dd including leading zeroes (2013-08-01), can seemingly make range queries work. there several reasons why databases have dedicated date datatypes!