Problem with the use of TOP 1 in a query

Question

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."

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?

Answer 1

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?

Answer 2

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.

Answer 3

It seems that auto-parameterization is to blame for the inconsistency.

Books Online documents that DATE, TIME, DATETIME2, and DATETIMEOFFSET use CONVERT style 121 by default, whereas style 0 is used for DATETIME and SMALLDATETIME. Someone forgot to update the auto-parameterization rules for the new types :)

Where the query can be auto-parameterized, style 0 is erroneously applied to the new DATE/TIME types if an implicit conversion, or an explicit conversion with a specified style takes place. The query without TOP is auto-parameterized (parameter [@1] appears instead of the TIME literal). TOP is one of the (many) query features that prevents auto-parameterization.

The obvious workaround is to always specify a desired style when using CONVERT.