sql server lead - problems with date

date, lead, sql, sql-server

Solution

You can use DATEADD() in this query. Also the default value of LEAD for data type can't be `0` so I've changed it to NULL or you can use any DATE constant.

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

SQLFiddle demo

Problem

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)

Original source