Querry中的问题

本文介绍了Querry中的问题-在操作符之间使用两个日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想检查两个日期时间值之间的特定日期. 例如 如果

开始日期= 2012-04-23 00 : 00 : 00 . 000 结束日期= 2012-04-26 00 : 00 : 00 . 000

我正在使用此Querry- 从离开在 ' 2012/04/23'之间，在convert(VARCHAR，startdate， 103 )并进行转换(VARCHAR，结束日期， 103 );

效果很好. 但是如果结束日期是从开始日期到下个月，则此查询返回null. 例如

开始日期= 2012-04-23 00 : 00 : 00 . 000 结束日期= 2012-05-04 00 : 00 : 00 . 000

查询返回null. 在此先感谢.

解决方案

之所以会发生这种情况，是因为将日期转换为varchar就是在进行字符串比较.通过不将日期转换为varchar来尝试此操作. 像这样的东西

选择离开员工身份来自离开其中 转换(日期时间，' [/Edit]

如果要在运算符之间使用日期进行比较，则必须在结束日期中设置最大时间. 就像enddate = 2012-05-04 23:59:59.999

您应该始终使用参数化查询.它不仅可以避免SQL Injection攻击，而且还可以使代码保持整洁和容易. 试试:

DECLARE @ leaveDate DateTime @ leaveDate = ' 2012年4月23日' 选择离开员工身份来自离开其中 @ leaveDate 在开始日期和结束日期

之间

Hi, I want to check a particular date between two datetime values. For Example if

startdate = 2012-04-23 00:00:00.000 enddate = 2012-04-26 00:00:00.000

i am using this querry-

select leaveemployeeid from leave where '23/04/2012' between convert(VARCHAR, startdate, 103) and convert(VARCHAR, enddate, 103);

it works fine. but if enddate is to next month from startdate then this querry return null. for example

startdate = 2012-04-23 00:00:00.000 enddate = 2012-05-04 00:00:00.000

querry return null. Thanks in Advance.

解决方案

This may happen because by converting the dates to varchar you are doing a string comparision. Try this by not converting the dates to varchar. [Edit] something like this

select leaveemployeeid from leave where convert(datetime,'23/04/2012') between convert(datetime,'2012-04-23 00:00:00.000') and convert(datetime,'2012-05-04 00:00:00.000') or select leaveemployeeid from leave where convert(datetime,'23/04/2012') between startdate and enddate -- where startdate and enddate are date fields.

[/Edit]

If you want to use between operator to compare date, in end date you will have to set maximum time. Like enddate = 2012-05-04 23:59:59.999

You should always use parameterized query. It not just avoid SQL Injection attack, but also maintains code clean and easy. Try:

DECLARE @leaveDate DateTime @leaveDate = '23/04/2012' select leaveemployeeid from leave where @leaveDate between startdate and enddate