当使用带变量和不带变量的查询时，会产生不同的结果

I have a query which filter records in a specific datetime range. For testing purpose i create a query with variables and it is not returning the expected result.

我有一个查询，它筛选特定日期时间范围内的记录。为了测试目的，我创建了一个带有变量的查询，它没有返回预期的结果。

Here is the query:

在这里查询:

Declare @vTimeFrom datetime = '2015-04-22 20:00:00.000'
Declare @vTimeTo datetime =   '2015-04-23 08:00:00.000'
Declare @pTime  datetime = '2015-04-22 21:00:00.000'

Select @pTime
where Convert(Varchar(5),@pTime,108) 
BETWEEN Convert(Varchar(5),@vTimeFrom,108) and Convert(Varchar(5),@vTimeTo,108)

It outputs:

输出:

No record found

没有发现记录

The above query returns nothing.

上面的查询不返回任何内容。

But consider this query :

但是考虑一下这个问题:

Declare @vTimeFrom datetime = '2015-04-22 20:00:00.000'
Declare @vTimeTo datetime =   '2015-04-23 08:00:00.000'
Declare @pTime  datetime = '2015-04-22 21:00:00.000'


Select @pTime
where Convert(Varchar(5),'2015-04-22 21:00:00.000',108) 
between Convert(Varchar(5),'2015-04-22 20:00:00.000',108)  
and Convert(Varchar(5),'2015-04-23 08:00:00.000',108)

It outputs:

输出:

April, 22 2015 21:00:00

2015年4月22日21:00:00

3 个解决方案

#1

Convert(Varchar(5),'2015-04-22 21:00:00.000',108) is actually just left('2015-04-22 21:00:00.000', 5). So in first case you're checking time and in second case you're checking strings.

Convert(Varchar(5)，'2015-04-22 21:00:01 .000'，108)实际上是向左的('2015-04-22 21:00:02 . 00.000'，5)所以在第一种情况下你检查时间，在第二种情况下你检查字符串。

Declare @vTimeFrom datetime = '2015-04-22 20:00:00.000'
Declare @vTimeTo datetime =   '2015-04-23 08:00:00.000'
Declare @pTime  datetime = '2015-04-22 21:00:00.000'

select
    convert(Varchar(5),@pTime,108),
    Convert(Varchar(5),@vTimeFrom,108),
    Convert(Varchar(5),@vTimeTo,108),
    Convert(Varchar(5),'2015-04-22 21:00:00.000',108),
    Convert(Varchar(5),'2015-04-22 20:00:00.000',108),
    Convert(Varchar(5),'2015-04-23 08:00:00.000',108)

------------------------------------------------------
21:00   20:00   08:00   2015-   2015-   2015-

#2

Select Convert(Varchar(5),'2015-04-22 21:00:00.000',108), Convert(Varchar(5),@pTime,108) , @pTime

gives you the answer:

给你答案:

2015- | 21:00 | 2015-04-22 21:00:00

2015- | 21:00 | 2015-04-22 21:00:00

The first direct formatting is assuming varchar convert and thus ingnoring the style attribute while the second convert is assuming datetime.

第一个直接格式化是假设varchar转换并因此赋值样式属性，而第二个转换是假设datetime。

To get the example without variables working you can use

要获得不使用变量的示例，您可以使用

Convert(Varchar(5), (cast ('2015-04-22 21:00:00.000' as datetime)),108)

to make sure convert is converting from datetime.

要确保转换是从datetime转换而来的。

#3

Those formats are for date types, they don't work for strings. So they returning different substrings.

这些格式是用于日期类型的，它们不能用于字符串。它们返回不同的子字符串。

Declare @vTimeFrom datetime = '2015-04-22 20:00:00.000'
Select Convert(Varchar(5),@vTimeFrom,108), 
Convert(Varchar(5),'2015-04-22 20:00:00.000', 108)

Output:

输出:

20:00   2015-

Here is fiddle: http://sqlfiddle.com/#!6/9eecb/4727

这是小提琴:http://sqlfiddle.com/ ! 6/9eecb / 4727

#1