3. you should modify Diff to DateDiff
Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From [LinkedServer].[testdb].[dbo].[VTABLE]
Where DateDiff(Day,EffectiveDate,getDate())<1
sELECT * FROM @temp
-- VTABLE records:
Code EffectiveDate Rate DDescription
cd1 2009-01-01 00:00:00.000 100 sql linked to sql
cd2 2009-01-02 00:00:00.000 200 sql linked to sql
cd3 2009-01-03 00:00:00.000 300 sql linked to sql
cd4 2009-01-21 00:00:00.000 400 sql linked to sql
-- Result:
cd4 2009-01-21 00:00:00.000 400.000000000
Declare @temp Table(Code Char(3),EffectiveDate Datetime,Rate Decimal(12,9))
Insert Into @temp(Code,EffectiveDate,Rate)
Select Code,EffectiveDate,Rate
From [LinkedServer].[testdb].[dbo].[VTABLE]
Where DateDiff(Day,EffectiveDate,getDate())<1
sELECT * FROM @temp
-- VTABLE records:
Code EffectiveDate Rate DDescription
cd1 2009-01-01 00:00:00.000 100 sql linked to sql
cd2 2009-01-02 00:00:00.000 200 sql linked to sql
cd3 2009-01-03 00:00:00.000 300 sql linked to sql
cd4 2009-01-21 00:00:00.000 400 sql linked to sql
-- Result:
cd4 2009-01-21 00:00:00.000 400.000000000