4

I tried to write a ms sql code that gives you workers work time in minutes. It takes two input as date. But it always return 0. What did i do wrong?

Create FUNCTION getMinutesWork
(
     @inp1 date,
     @inp2 date
)
RETURNS int
As
Begin
     DECLARE @outp int = DATEDIFF(mi,@inp1,@inp2)
RETURN(@outp)
End

And i tried to run it with this

SELECT dbo.getMinutesWork('2004-10-18 07:53:31','2004-10-18 10:13:35') AS WorkTime

It returns 0. Im so newbie on sql. Can you help?

calcuku
  • 67
  • 6

2 Answers2

4

You are using input parameters of type DATE, which have a precision of one day. Hence you are losing the time portion.

Use DATETIME, or have a read of this article and decide what is right for you: http://blogs.msdn.com/b/cdnsoldevs/archive/2011/06/22/why-you-should-never-use-datetime-again.aspx

Works:

DECLARE @p1 DATETIME = '2004-10-18 07:53:31'
DECLARE @p2 DATETIME = '2004-10-18 10:13:35'
SELECT DATEDIFF(mi,@p1,@p2)

Doesn't work (well, it does but will get difference in whole days):

DECLARE @p1 DATE = '2004-10-18 07:53:31'
DECLARE @p2 DATE = '2004-10-18 10:13:35'
SELECT DATEDIFF(mi,@p1,@p2)
GinjaNinja
  • 776
  • 6
  • 18
1

I think your issue is related to data types provided.

You're using date which doesn't support minutes. Please use DATETIME2.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107