sql server - How to get the previous row value with an ORDERING BY DATE? -

when run here below code error :

msg 206, level 16, state 2, line 14 operand type clash: int incompatible date

here below code here above error:

declare @tbl table ( cat1 nvarchar(500), cat2 nvarchar(500), somedate date );  insert @tbl values ('a','b','2016-01-01'), ('a','b','2016-01-02'), ('a','b','2016-01-03'), ('a','b','2016-01-04'), ('a','b','2016-01-05');  select  cat1, cat2, somedate, lag(somedate,1,0) on (partition cat1, cat2 order somedate asc) previoussomedate @tbl; 

and here below expected result (the previous row value of [somedate]) :

('a','b','2016-01-01',''), ('a','b','2016-01-02','2016-01-01'), ('a','b','2016-01-03','2016-01-02'), ('a','b','2016-01-04','2016-01-03'), ('a','b','2016-01-05','2016-01-04'); 

lag(somedate,1,0) means: return previous value of column somedate , if there no "previous" column (because it's first row) return value 0.

if was possible, have integer value in column first row , date value other rows. values of 1 column must have same data type, sql server complains, "int incompatible date"

you need use date default value (the third parameter). apparently don't want value, use lag(somedate) return null first row.