1

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
Nad
  • 4,605
  • 11
  • 71
  • 160
  • 1
    Format `MM/DD/YYYY`? – jarlh Sep 27 '16 at 07:24
  • 2
    @jarlh: no, I want in `dd/MM/yyy` format. I have used 103 for that – Nad Sep 27 '16 at 07:24
  • I'm just guessing that, due the language of the system, it's taking the data with format: "month/day/year". Try `2016-09-13` – Rumpelstinsk Sep 27 '16 at 07:25
  • @nad that would be the format AFTER conver function ends. But, convert function recieves a string, ¿Which format is considered to parse that string INSIDE convert function?. Convert function analices the string taking the datetime-format default on the system. So it's perfectly possible that convert function, parse `13` as month as @jarlh said – Rumpelstinsk Sep 27 '16 at 07:27
  • @nad The `style` attribute in `CONVERT(data_type(length),expression,style)` only applies for date or time data type. https://msdn.microsoft.com/en-us/library/ms187928.aspx – Nebi Sep 27 '16 at 07:51

2 Answers2

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'}
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • which 1 should I use, first one or second one.. I am confused – Nad Sep 27 '16 at 07:56
  • @nad Sorry, there was a `convert` to much... See my update – Shnugo Sep 27 '16 at 08:01
  • what's `d` here ?? – Nad Sep 27 '16 at 09:35
  • @nad. This is `ODBC`-format. Find details [here](http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i/39504506#39504506) and [here](https://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx). – Shnugo Sep 27 '16 at 09:38
  • superb explanation on the links. Thanks , will rectify and let you know – Nad Sep 27 '16 at 09:53
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.

Serg
  • 22,285
  • 5
  • 21
  • 48
  • @Shnugo It's just a matter of remembering BETWEEN is inclusive (>= ,<=) for dates, integers, whatever type. – Serg Sep 27 '16 at 08:30
  • Not really... The very last moments before and after midnight can be a problem... taking `BETWEEN {d'2016-09-17'} AND {d'2016-09-18'}` will **inlcude** a record of exactly midnight + tiny time of the **next** day, while `BETWEEN {d'2016-09-17'} AND {ts'2016-09-17 23:59:59'}` could miss records of the very last second (even if specified with milliseconds...). Admitably a very small problem, but not necessary... – Shnugo Sep 27 '16 at 08:34