SQLTeam.com | Weblogs | Forums

SQL --date incompatible with INT


#1

Hi ,

I have below query I am doing but its failing for" Operand type clash: date is incompatible with int".
Thank you!

fields type:
FDT =date
FSEQ= varchar
Rate= Decimal
EMPID =varchar

SELECT EMPID
, RATE
,FSEQ
FDT , case when lead is null then '2500-12-31' else lead -1 end as ef_dt
FROM (SELECT EMPID, RATE, FSEQ, FDT , lead(FDT) over (partition by EMPID order by FDT, fseq) as lead --- this returns INT

FROM job j) j1
WHERE FDT <= getdate()


#3

Try:

SELECT
     EMPID
   , RATE
   , FSEQ FDT
   , case 
        when lead is null then '2500-12-31' 
        else DateAdd(DAY, -1, lead ) 
     end as ef_dt
FROM 
   ( SELECT 
         EMPID
       , RATE
       , FSEQ
       , FDT
       , lead(FDT) 
             over (partition by EMPID order by FDT, fseq) as lead --- this returns INT
     FROM job j
   ) j1
WHERE 
   FDT <= getdate();

#4

Thanks Jotorre, in fact after I posted I tried bunch of things and did use the dateadd function and I am not getting the error. :slight_smile:
Pasi.