nad nad - 1 year ago 70
SQL Question

Why date is not taking 13/09/2016

I am checking condition for

but it is not showing me data for
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

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
BETWEEN convert(varchar,'12/09/2016',103)
AND convert(varchar,'13/09/2016',103)
and b.mkey=2357

Answer Source

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'}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download