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)

GarethD · Answer 1 · 2014-05-27T12:27:11.900

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.

score 3 · Answer 2 · edited May 27 '14 at 14:29

3

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

edited May 27 '14 at 14:29

Lipis

19,958
18
88
117

answered May 27 '14 at 13:01

georstef

1,348
3
12
18

score 2 · Accepted Answer · answered May 27 '14 at 12:07

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

score 0 · Answer 4 · answered May 27 '14 at 12:23

Will this help you, The error was happening when 0 was given in place of where a date field was expected.

I replaced lead(von,1,0) with lead(von,1,'1900-01-01')

See another code with same result.

SET DATEFORMAT ymd
DECLARE @Atext TABLE  
    (id INT, bez VARCHAR(10), von DATE); 

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', '2099-05-01'); 

SELECT  id, 
        bez, 
        von,
        lead(von,1,'1900-01-01') OVER (PARTITION BY id ORDER BY von) AS bis
FROM    @Atext
ORDER   by 
        id, 
        Von

Result

enter image description here

sql server lead - problems with date

4 Answers4