Calculate the difference in minutes between startTime and endTime

Question

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

The first paramater of DateDiff is the date part you want, you would use MINUTE for your purposes — Brad, May 23 '18 at 12:34
In addition to Brad's input, make sure that @CallerStartTime, as a datetime variable, includes both time AND date, or at least that neither CallStartTime nor CallEndTime include a date part. — George Menoutis, May 23 '18 at 12:35
You can try using this link: https://stackoverflow.com/questions/26991807/calculate-time-difference-in-minutes-in-sql-server — Aura, May 23 '18 at 12:40
If you want the 15 minutes to be precise, and represent 900 seconds, do the time difference in seconds, datediff counts the boundaries crossed, so a value which is only 14 minutes 10 seconds apart, can appear to cross 15 minute boundaries. — Andrew, May 23 '18 at 12:42
Have you checked the [docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017) for `DATEDIFF()`? — HoneyBadger, May 23 '18 at 12:42

score 2 · Accepted Answer · answered May 23 '18 at 12:45

2

Function DATEDIFF wants as first parameter the portion of time.

The correct use of it is:

DATEDIFF(minute, @CallStartTime, @CallEndTime)

answered May 23 '18 at 12:45

Joe Taras

15,166
7
42
55

score 1 · Answer 2 · answered May 23 '18 at 12:45

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?

score 1 · Answer 3 · answered May 23 '18 at 12:48

1

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];

answered May 23 '18 at 12:48

Tyron78

4,117
2
17
32

Calculate the difference in minutes between startTime and endTime

3 Answers3