Why date is not taking 13/09/2016

Question

I am checking condition for 12/09/2016 to 13/09/2016 but it is not showing me data for 13/09/2016 and giving error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is my query

SELECT DISTINCT  
   b.mkey ,  a.N_UserMkey, cuser_id,isnull(a.N_UserMkey,cuser_id) aa,
   ISNULL(b.first_name + ' ', '')  
   + ISNULL(b.last_name, '') NAME, convert(varchar,a.U_datetime,103) Action_Date
  FROM      inward_doc_tracking_trl a  
   INNER JOIN user_mst b ON isnull(a.N_UserMkey,cuser_id) = b.mkey  
  WHERE 
  convert(datetime,a.U_datetime,103) 
    BETWEEN convert(varchar,'12/09/2016',103)
  AND convert(varchar,'13/09/2016',103)
   and b.mkey=2357

Answer 1

You need to convert to DATETIME

...
BETWEEN convert(datetime,'12/09/2016',103)
  AND convert(datetime,'13/09/2016',103)

currently the query just leaves BETWEEN arguments as VARCHAR and then the server needs to compare them with convert(datetime,a.U_datetime,103). At that moment they are converted to DATETIME with no format specified.

Answer 2

I'm not sure, but it seems that you are cummulating several mistakes here:

• Do not check for date ranges with BETWEEN. This is very erronous, due to the time-portion of a datetime. Often forgotten... You might read this great blog by Aaron Betrand
• Never use literal dates in culture specific formats. You might read this (and other answers there)
• Compare data always in the type needed. You are converting dates to string just to compare them alphanumerically?
• in convert(varchar,'12/09/2016',103) you are using varchar without a length... One more bad habit to kick

Try to change your WHERE clause to this (all datetimes on 12th of September, but not on 13th)

WHERE a.U_datetime >= {d'2016-09-12'} AND a.U_datetime<{d'2016-09-13'}

or this (all datetimes of 12th and of 13th September)

WHERE a.U_datetime >= {d'2016-09-12'} AND a.U_datetime<{d'2016-09-14'}