CODEWITHSUNDEEP
sql-serverfunction
Kam Par
Kam Par

Reputation: 91

Operand data type date is invalid for subtract operator

I have a field in my table called [LastDate] with DataType Date. and I am going to write a function which compute the [LastDate]-@PassedParameter, but the error happen :

Operand data type date is invalid for subtract operator.

I don't know why?

hara is the function:

CREATE FUNCTION Salman( @Date  date )
RETURNS TABLE 
AS
RETURN 
(

SELECT TOP 1000 [ID]
      ,[Name]
      ,[LastDate]
      ,[Rule]
      ,[CoA]
  FROM [Scheduling_Employee].[dbo].[Group]
  where ([LastDate]-@Date)%[Rule]=0  
)
GO

Upvotes: 7

Views: 23438

Answers (3)

Edward
Edward

Reputation: 1

I believe the format of your two dates is not exactly the same. This should work.

CREATE FUNCTION Salman( @Date  date )
RETURNS TABLE 
AS
RETURN 
(

SELECT TOP 1000 [ID]
      ,[Name]
      ,[LastDate]
      ,[Rule]
      ,[CoA]
  FROM [Scheduling_Employee].[dbo].[Group]
  where (cast([LastDate] as datetime)-cast(@Date as datetime))%[Rule]=0  
)
GO

Upvotes: 0

MelgoV
MelgoV

Reputation: 656

Try this:

CREATE FUNCTION Salman( @Date  date )
RETURNS TABLE 
AS
RETURN 
(

SELECT TOP 1000 [ID]
  ,[Name]
  ,[LastDate]
  ,[Rule]
  ,[CoA]
FROM [Scheduling_Employee].[dbo].[Group]
where (Datediff(dd,[LastDate],@Date))%[Rule]=0  
)
GO

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

You may try using the DATEDIFF function.

DATEDIFF ( datepart , startdate , enddate )

So in your case you may change like this:

where DATEDIFF(dd,LastDate,@Date)%[Rule]=0
               ^^--Change this to mm,qq whatever you want.

Upvotes: 7

Related Questions