sql server - finding data lying between a specific date range in sql -

i want find records database lie between user input date range(say between 10/2/2008 26/9/2024). tried using

select name     ,type     ,comp_name     ,batch_no     ,shelf     ,mfg_date     ,exp_date     ,qty     ,vat     ,mrp stock_local      convert(varchar(20), exp_date, 103)          between convert(varchar(20), @medicineexp_date, 103)             , convert(varchar(20), @medicineexpdate, 103) 

but query need enter perfect date range available in database, not giving me data lying in between date entered.

thanks in advance

since poolr designed schema there isnt going decent/efficient solution this.

in sql server if storing date or date & time data. use data or datetime datatypes columns.

in case trying compare string passed date. , when tried convert string (date) date datatype didnt correctly.

my suggestion add new columns table date datatype , update these columns existing date/string values.

for can convert date(string) date datatype using following code.

declare @medicineexp_date date = 'somevalue1' declare @medicineexpdate  date = 'somevalue1'  select query.... tablename      cast(           right(exp_date, 4)         +substring(exp_date,charindex('/',exp_date)+1,2)         +left(exp_date,2)          date)  >= @medicineexp_date , cast(           right(exp_date, 4)          +substring(exp_date,charindex('/',exp_date)+1,2)          +left(exp_date,2)           date)  <= @medicineexpdate 

note

this solution expected results inefficient method. not make use of indexses on exp_date column if have buffed index on column.