为什么日期不采取 13/09/2016 [英] Why date is not taking 13/09/2016

问题描述

我正在检查 12/09/2016 到 13/09/2016 的条件，但它没有显示 13/09/2016 并给出错误

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

将 char 数据类型转换为日期时间数据类型导致日期时间值超出范围.

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

这是我的查询

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

推荐答案

我不确定，但您似乎在这里累积了几个错误:

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

• 不要使用 BETWEEN 检查日期范围.由于日期时间的时间部分，这是非常错误的.经常被遗忘...你可能 阅读 Aaron Betrand 的这篇精彩博客
• 切勿以特定于文化的格式使用文字日期.您可以阅读本文(以及那里的其他答案)
• 总是以所需的类型比较数据.您将日期转换为字符串只是为了比较它们字母数字?
• 在 convert(varchar,'12/09/2016',103) 中，您使用的是没有长度的 varchar... 还有一个要踢的坏习惯

• 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

尝试将您的 WHERE 子句更改为此(9 月 12 日的所有日期时间，但不是 13 日)

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'}

或此(9 月 12 日和 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'}

这篇关于为什么日期不采取 13/09/2016的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！