在查询中使用TOP 1的问题 [英] Problem with the use of TOP 1 in a query
问题描述
我写了以下查询来获取日期,删除它的时间部分并添加我想要的时间. 如果我在不使用TOP子句的情况下运行此查询,则效果很好.但是,当我添加它时,它返回以下异常:从字符串转换日期和/或时间时转换失败."
I wrote the following query to obtain a date, remove it's time part and add the time I wanted. If I run this query without the TOP clause, it works well. But when I add it, it returns the following exception: "Conversion failed when converting date and/or time from character string."
以下是查询:
SELECT TOP 1
CONVERT(DateTime, (CONVERT(varchar(50),CONVERT(Date, VRSAS.EventOn))
+ ' ' +
CONVERT(varchar(50), CONVERT(Time, '23:30')))) E
FROM ViewRangeSheetActualStatus VRSAS
Where VRSAS.EventOn <= '2010-07-31'
AND VRSAS.[Status] = 1
order by VRSAS.RangeSheet
EventOn字段的类型为DateTime.
The field EventOn is of type DateTime.
可能会发生什么?
推荐答案
为此目的,我已经很容易地重现了.我发现使用DATEADD
解决了该问题
I've reproduced quite easily this end. I found using DATEADD
resolved it
DATEADD(MINUTE, 23*60 + 30, CONVERT(DATETIME,CONVERT(DATE, VRSAS.EventOn)))
但是我实际上还不确定为什么.重现步骤如下.
But I'm not actually sure why yet. Steps to reproduce below.
CREATE TABLE ViewRangeSheetActualStatus
(EventOn DATETIME,
[Status] BIT,
RangeSheet INT
)
INSERT INTO [dbo].[ViewRangeSheetActualStatus]([EventOn], [Status], [RangeSheet])
SELECT '20100903 11:02:39.517', 1, 1 UNION ALL
SELECT '20100731 11:03:23.577', 1, 1 UNION ALL
SELECT '20100731 00:00:00.000', 1, 1
/*Selects ALL records - No error*/
SELECT
CONVERT(DateTime, (CONVERT(varchar(50),CONVERT(Date, VRSAS.EventOn))
+ ' ' +
CONVERT(varchar(50), CONVERT(Time, '23:30')))) E
FROM ViewRangeSheetActualStatus VRSAS
/*Selects top (1) record - Error!*/
SELECT top (1)
CONVERT(DateTime, (CONVERT(varchar(50),CONVERT(Date, VRSAS.EventOn))
+ ' ' +
CONVERT(varchar(50), CONVERT(Time, '23:30')))) E
FROM ViewRangeSheetActualStatus VRSAS
看看执行计划中的ComputeScalar
属性,两者是不同的.
Looking at the ComputeScalar
properties in the execution plan the two are different.
全部
(CONVERT(datetime,(CONVERT(varchar(50),CONVERT(date,[ViewRangeSheetActualStatus].
[EventOn] as [VRSAS].[EventOn],0),0)+[@1])+CONVERT(varchar(50),CONVERT(time(7),
[@2],0),0),0))
前1名
(CONVERT(datetime,(CONVERT(varchar(50),CONVERT(date,[ViewRangeSheetActualStatus].
[EventOn] as [VRSAS].[EventOn],0),121)+' ')+'23:30:00.0000000',0))
在最终转换为datetime
之前,第一个生成包含以下内容的varchar
Before the final conversion to datetime
the first one produces a varchar containing the following
------------------------------
Sep 3 2010 11:30PM
Jul 31 2010 11:30PM
Jul 31 2010 11:30PM
第二个版本会生成一个包含varchar的
The second version produces a varchar containing
------------------------------
2010-09-03 23:30:00.0000000
是.0000000
导致问题回退到datetime
的原因.我不知道为什么在查询中添加TOP
会导致这种完全不相关的行为更改.
It is the .0000000
that causes the problem casting back to datetime
. I have no idea why the addition of TOP
to the query would cause this completely unrelated change in behaviour.
这篇关于在查询中使用TOP 1的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!