如何在sql server中验证日期 [英] how to validate the date in sql server

问题描述

表格如下

预定描述移动状态
 2014-02-06 16：48：09.000 AFF xxxx是
 2014-02 -07 16：48：09.000 MFA xxxx是

从上表中我想查看预定日期（日期为6日和7日）

对于我写的查询如下

 选择计划，描述，移动，状态来自 Shortcode_Course_SMS 其中​​ Msgdelivered> '  20140205' 和 Msgdelivered< '  20140208' 

当我执行以上查询输出如下

预定描述移动状态
 2014-02-06 16：48：09.000 AFF xxxx是
 2014-02- 07 16：48：09.000 MFA xxxx是

但是当我按如下方式编写上述查询时

 < span class =code-keyword> select  Scheduledate，Description，Mobile，Replymsg 来自 Shortcode_Course_SMS 其中 Msgdelivered = '  20140206' 和 Msgdelivered = '  20140206' 

i想要查看特定日期例如第6个日期（2014-02-06 16：48：09.000）

当我在上面运行时查询输出是空的n o记录显示

预定描述移动状态

 选择预定，说明，移动，回复来自 Shortcode_Course_SMS 其中 Msgdelivered = '  20140206' 和 Msgdelivered = '  20140206' 

来自我的上述查询有什么问题请帮帮我。

问候，

Narasiman P.

解决方案

有两个原因：首先你的AND条件，这可能要求一个日期同时有两个不同的值（我建议你想要OR代替）



其次因为=在每一方都需要完全相同的值 - 因为它们是DATETIME val这意味着他们需要一直到微秒。由于你没有指定时间，'20140206'是2月6日的午夜，这与2月6日16：48：09.000不符。



你可以更改您的查询以使用

  SELECT  Scheduledate，Description，Mobile，Replymsg 
  FROM  Shortcode_Course_SMS 
  WHERE  DATEADD（dd， 0 ，DATEDIFF（dd， 0 ，Msgdelivered））= '  20140206' 
 或 DATEADD（dd， 0 ，DATEDIFF（dd） ， 0 ，Msgdelivered））= '  20140207' 

还有一种更好的方式来编写第一个查询：

  FROM  Shortcode_Course_SMS 
  WHERE  Msgdelivered  BETWEEN  '  20140205'  AND  '  20140208'  

Table as follows

Scheduledate               Description    Mobile     Status
2014-02-06 16:48:09.000       AFF           xxxx       Yes
2014-02-07 16:48:09.000       MFA           xxxx       Yes

From the above table i want to view Scheduledate (date from 6th and 7th date)
for that i written query as follows

select Scheduledate,Description,Mobile,Status from Shortcode_Course_SMS where Msgdelivered > '20140205' and Msgdelivered < '20140208'

When i execute the above query output as follows

Scheduledate               Description    Mobile     Status
2014-02-06 16:48:09.000       AFF           xxxx       Yes
2014-02-07 16:48:09.000       MFA           xxxx       Yes

But when i write the above query as follows

select Scheduledate,Description,Mobile,Replymsg  from Shortcode_Course_SMS where Msgdelivered = '20140206' and Msgdelivered = '20140206'

i want to view particular date for example 6th date(2014-02-06 16:48:09.000)
When i run above query output is empty no record is shown

Scheduledate               Description    Mobile     Status

select Scheduledate,Description,Mobile,Replymsg  from Shortcode_Course_SMS where Msgdelivered = '20140206' and Msgdelivered = '20140206'

from my above query what is the problem please help me.
Regards,
Narasiman P.

解决方案

There are two reasons: first your AND condition, which potentially requires that a date has two different values at the same time (I would suggest you want OR instead)

Second because "=" needs exactly the same value on each side - and since these are DATETIME values that means they need to be the same right down to the microsecond. Since you don;t specify a time, '20140206' is midnight on the 6th Feb, which does not match 16:48:09.000 on the 6th Feb.

You can change your query to use

SELECT Scheduledate,Description,Mobile,Replymsg 
FROM Shortcode_Course_SMS 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, Msgdelivered)) = '20140206' 
   OR DATEADD(dd, 0, DATEDIFF(dd, 0, Msgdelivered)) = '20140207'

There is also a better way to write your first query:

FROM Shortcode_Course_SMS
WHERE Msgdelivered BETWEEN '20140205' AND '20140208'

这篇关于如何在sql server中验证日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！