Problem with the use of TOP 1 in a query

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开发者_如何学JAVA: "Conversion failed when converting date and/or time from character string."

Here is the query:

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

The field EventOn is of type DateTime.

What could be going on?

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 

Looking at the ComputeScalar properties in the execution plan the two are different.

All

(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))

Top 1

(CONVERT(datetime,(CONVERT(varchar(50),CONVERT(date,[ViewRangeSheetActualStatus].
 [EventOn] as [VRSAS].[EventOn],0),121)+' ')+'23:30:00.0000000',0))

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

The second version produces a varchar containing

------------------------------
2010-09-03 23:30:00.0000000

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.

A strange one, have you verified that it really does work with out the "top one" restriction? Sometimes the "top one" just makes the error more visible. If you have many, many rows and you remove the "top one" restriction, the query may give the impression of working, but in the background its still spooling the results and hasn't hit the line that causes the problem.

Is EventOn non-nullable, that could be a prime reason. If so, put a non null check first.

Also, what is the type "RangeSheet", what data type is it and can that hold nulls?