I suspect your problem is with DATALENGTH.

Try using varchar(MAX) instead of text and simplifying your expressions.

(You do not need the length of the substring, just any number greater than its...

If only the metadata is required by an application, then use:

SET FMTONLY ON;

Or a more simplistic approach...

-- *** Test Data ***

CREATE TABLE #t

(

ID int NOT NULL

,PID int NOT NULL

,[Name] varchar(25) NOT NULL

)

INSERT INTO #t

SELECT 1, 0, 'Ad'

UNION ALL SELECT 2, 0, 'Area'

UNION...

Zero is the correct answer as an integer divided by an integer is an integer.

If you want a floating point answer, make one for the constants a float.

eg SELECT 1.0/6;

Edit...

I think you are going to have to calculate the hour and minute values separately and then combine them.

You will probably have to use both a Calendar and a tally...

Under some curcumstances the order of a select statement,...

I am not sure why ID is in your test data, but something like the following may help:

-- *** Test Data ***

CREATE TABLE #t

(

ReturnDate datetime NOT NULL

)

INSERT INTO #t

VALUES ('20101031')

,('20101130')

,('20110131');

--...

This article may be of interest:

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

The best way to ensure the correct collation for a temp table is to always use SELECT INTO to create them.

(This also has the advantage that if the column width...

sp_executesql behaves like a dynamic stored procedure; you cannot pass an object name as a parameter.

To use sp_executesql, try something like:

SELECT @sql =

'SELECT COUNT(1) '

+ 'FROM doc_lks '

+ 'INNER JOIN...

Hi Iain,

Thanks for the performance comparison.

We both seem to expect the quirky update to run faster. As it is non-relational I prefer to let people use their own judgement on...

Sunny,

If you want the groups to show then:

1. like Iain, I suspect the quirky update will be quickest.

The following article, by Jeff Moden, gives the details; you should read the...

SELECT @@options

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

You will need to watch boundary conditions, but try something like the following:

WITH EmpLoginTime

AS

(

SELECT EmpName

,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY LoginTime) AS EmpOrder

,LoginTime

FROM dbo.logintime

)

,EmpTimes

AS

(

SELECT T1.EmpName, T1.EmpOrder, T1.LoginTime AS StartTime

,CASE

WHEN...

If you have space in tempdb, try using read commited snapshot isolation.