I dont know why it SQL Server DATEDIFF give me this error?

[accesser2003]

I have a SQL Server database. I buildt a stored procedure FindPSF as follow:

CREATE PROCEDURE dbo.FindPSF(@Ix int)
AS SELECT Indx, AttDay, Start, Finish, DATEDIFF(day, Start, Finish)
FROM dbo.SelectedEmployeesWorkMode
WHERE (Indx = @Ix) AND (NOT (Start IS NULL)) AND (NOT (Finish IS NULL)) AS count_of_days

The structure of the tabel [dbo.SelectedEmployeesWorkMode] is as follow:

CREATE TABLE [dbo].[SelectedEmployeesWorkMode] (
[Indx] [int] NOT NULL ,
[AttDay] [smalldatetime] NOT NULL ,
[WorkMode] [nvarchar] (50) NOT NULL ,
[RosterCode] [nvarchar] (50) NULL ,
[Start] [nvarchar] (50) NULL ,
[Finish] [nvarchar] (50) NULL
)


When I run the PSF stored procedure, it gives me this strange error:

"Arithmetic overflow error converting expression to data type datetime"

It is really strange error as the DATEDIFF function is a simple one. Please help. Thanks in advance.

 

[pbaldy]

What do the values in start and finish look like? The function expects datetime values, while those are nvarchar. The error implies it's having trouble converting them to datetime values.

 

[boblarson]

Yeah, my question would by why are you storing date/time as nvarchar?

 

[accesser2003]

Correct. I will try the Start, Finish as datetime.

However, what is the data type used if the fields are only time not datetime and not smalldatetime?

 

[pbaldy]

You'd still use datetime, though how do you expect to get a difference in days if the fields only hold time?

 

[boblarson]

You'd still use datetime, though how do you expect to get a difference in days if the fields only hold time?

True - even more important. Good catch Paul - I missed that one completely.