如何在sql server中验证日期

本文介绍了如何在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是 但是当我按如下方式编写上述查询时 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 followsScheduledate Description Mobile Status2014-02-06 16:48:09.000 AFF xxxx Yes2014-02-07 16:48:09.000 MFA xxxx YesFrom the above table i want to view Scheduledate (date from 6th and 7th date)for that i written query as followsselect Scheduledate,Description,Mobile,Status from Shortcode_Course_SMS where Msgdelivered > '20140205' and Msgdelivered < '20140208'When i execute the above query output as followsScheduledate Description Mobile Status2014-02-06 16:48:09.000 AFF xxxx Yes2014-02-07 16:48:09.000 MFA xxxx YesBut when i write the above query as followsselect 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 shownScheduledate Description Mobile Statusselect 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_SMSWHERE Msgdelivered BETWEEN '20140205' AND '20140208' 这篇关于如何在sql server中验证日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！