Querry中的问题-在操作符之间使用两个日期 [英] problem in querry-using two dates with between operator

问题描述

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

例如
如果

开始日期= 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来尝试此操作.


像这样的东西

 选择离开员工身份来自离开其中 转换(日期时间，'  2012/04/23')之间 转换(日期时间，'  2012-04-23 00:00:00.000') and  转换(' 或
选择离开员工身份来自离开其中 转换(日期时间，' 在开始日期之间- 其中开始日期和结束日期是日期字段. 

[/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

这篇关于Querry中的问题-在操作符之间使用两个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！