using LIKE and BETWEEN in sql query

Question

alter PROCEDURE [dbo].[select_User_attendance_master_Date]        
(        
@From_date NVarchar(100),  
@To_date NVarchar(100)            

)        
as        
 begin        
  select           

 Employee_Attendace_Code,        
 Convert(varchar(11),Employee_Attendance.Attendance_day,106) as Attendanceday  

    from Employee_Attendance        
(Convert(varchar(11),Employee_Attendance.Attendance_day,106) between '%'+@From_date+'%'    
 AND  '%'+@To_date+'%')     

end

this query is not working it is getting nothing in Attendance_day values are eg 2013-11-28

Answer 1

Please rethink your question. LIKE is great to operate over strings but ill not work well for dates. depending on your data it can return a from date is greater than to date.

Try to use date/datetimes variables, if you receive dates as a string, convert that dates to date/datetime data type and just put that values in the between.

And receive that dates parameters as date/datetime types if you can.

Answer 2

I believe you are looking for this? You where missing the WHERE clause and I corrected a typo for your first column selected.

ALTER PROCEDURE [dbo].[select_User_attendance_master_Date]        
(        
@From_date NVarchar(100),  
@To_date NVarchar(100)            

)        
AS        
 BEGIN        
    SELECT           
        Employee_Attendance_Code,        
        Employee_Attendance.Attendance_day as Attendanceday  
    FROM Employee_Attendance        
    WHERE Employee_Attendance.Attendance_day BETWEEN @From_date AND @To_date     

END