CODEWITHSUNDEEP
sqlsql-server
Matteo
Matteo

Reputation: 346

Calculate the difference in minutes between startTime and endTime

I have to calculate the difference from StartTime and EndTime. If the EndTime is less then 15 minutes that of StartTime I have to show an error.

CREATE PROCEDURE [Travel].[TravelRequirementValidate] 
@Action char(1)
,@CallId int
,@PhaseId int
,@ShipId int 
,@CallStartDate datetime
,@CallEndDate DATETIME
,@CallStartTime datetime 
,@CallEndTime datetime   
,@LanguageId int
,@SessionGroup nvarchar(100)
,@SessionPlace nvarchar(100)
,@ActiveFlg tinyint
,@WarningMessage nvarchar(300)=NULL output
,@Minutes int 
as
if @Action in ('I','U')
begin
   @Minutes=select DATEDIFF(@CallStartDate,@CallStartTime,@CallEndTime) from [Travel].[TravelRequirement] 
if @Minutes<=15
begin
  raiserror(3,11,1) --CallEndTime must be equals or higher than 15 minutes
  return
end
end

This code doesn't work. I've got an error for the first parameter of DATEDIFF (invalid parameter 1 specified for datediff).

How can I fix my code?

EDIT

I changed @Minutes=select DATEDIFF(@CallStartDate,@CallStartTime,@CallEndTime) from [Travel].[TravelRequirement]

in 

declare @Diff int
@Diff=select DATEDIFF(@Minutes,@CallStartTime,@CallEndTime) from [Travel].[TravelRequirement]

but I have the same error

Upvotes: 0

Views: 1607

Answers (3)

Tyron78
Tyron78

Reputation: 4187

I would suggest using seconds and devide by 60.0 - provides a more accurate result:

select @Minutes = datediff(second, @CallStartTime, @CallEndTime)/60.0
from [Travel].[TravelRequirement];

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The correct syntax for that expression would be:

select @Minutes = datediff(minute, @CallStartTime, @CallEndTime)
from [Travel].[TravelRequirement];

However, this does't make sense, because -- presumably -- the table has more than one row. Which row do you want?

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

Function DATEDIFF wants as first parameter the portion of time.

The correct use of it is:

DATEDIFF(minute, @CallStartTime, @CallEndTime)

Upvotes: 2

Related Questions