sql server lead - problems with date

Question

I habe a problem with the new lead olap function in sql server 2012.

CREATE TABLE Atext (id int, bez varchar(10), von date); 

GO

INSERT INTO Atext VALUES (1, 't1', '2001-01-01'), (1, 't2', '2012-01-01'), (2, 'a1', '2020-01-01'), (2,'a1' , '2030-01-01'), (2, 'b', '2040-05-01'), (2, 'a3', '2989-05-01'); 
GO

SELECT 
        id, 
        bez, 
        von,
        lead(von,1,0) over (partition by id ORDER BY von) -1 as bis
FROM 
        Atext
order by 
        id, 
        Von

The select query throws an error:

Msg 206, Level 16, State 2, Line 1 
Operand type clash: int is incompatible with date

Why is there a restrictions in terms of the data type datetime?

I know a workaround but it is not very nice:

SELECT
    id,
    bez,
    CAST(vonChar AS DATE) AS Von,
    CASE
        WHEN bisChar <> '0'
        THEN (DATEADD(DAY,-1,(CAST((
                    CASE
                        WHEN bisChar <> '0'
                        THEN vonChar
                        ELSE NULL
                    END)AS DATE)) ))
        ELSE NULL /*'9999-12-31'*/
    END AS Bis
FROM
    (
        SELECT
            id,
            bez,
            vonChar ,
            lead(vonChar,1,0) over (partition BY id ORDER BY vonChar) AS bisChar
        FROM
            (
                SELECT
                    id,
                    bez,
                    CAST(von AS VARCHAR(10)) vonChar
                FROM
                    Atext) tab ) tab2
ORDER BY
    id,
    Von

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

Answer 1

Using a date (or something that can be implicitly cast to a date) as the default for the lead function, and the DATEADD function would be a simpler work around:

SELECT 
        id, 
        bez, 
        von,
        DATEADD(DAY, -1, LEAD(von, 1, '19000101') 
                        OVER (PARTITION BY id ORDER BY von)) AS bis
FROM Atext
ORDER BY id, Von;

Or just not bother with a default. It is not required:

SELECT 
        id, 
        bez, 
        von,
        DATEADD(DAY, -1, LEAD(von, 1) 
                        OVER (PARTITION BY id ORDER BY von)) AS bis
FROM Atext
ORDER BY id, Von;

The problem is that you cannot cast an INT to a DATE. In expressions in SQL Server all parts of the expression must be of the same data type, so, if you do for example:

SELECT TOP 1 GETDATE() + number
FROM master..spt_values
WHERE type = 'p'

Behind the scenes SQL Server needs to decide whether to convert GETDATE() to an integer so all parts of the expression are integers, or 1 to a datetime so that all parts are datetimes. SQL Server's Data Type Precedence dictates that DATETIME has a higher precedence than INT, so SQL Server converts 1 to a datetime, this can be seen in the execution plan:

<ScalarOperator ScalarString="getdate()+[Expr1005]">
....
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime,[master].[dbo].[spt_values].[number],0)">

Which shows that the INT column master..spt_values.number is implicitly converted to a datetime, before being added to getdate()

For whatever reason (I looked extensively not long ago and found nothing to explain exactly why), there is no implicit or explicit conversion from int to date, you have to use date functions.

Answer 2

Same as all the other answers but with DATEADD as an argument of lead:

SELECT
  id, 
  bez,
  von,
  lead(DATEADD(DAY,-1, von),1, null) over (partition by id order by von)
from 
  Atext
order by
    id,
    Von

SQLFiddle example