MS Sql Datediff Function

Question

I tried to write ms sql code that gives you working time in minutes. It takes two entries as a date. But it always returns 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 using this

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

It returns 0. I am so new to sql. You can help?

Answer 1

You use input parameters of type DATE, which have an accuracy of one day. Therefore, you are losing some of the time.

Use DATETIME or read this article and decide what suits 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 gets the 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)

Answer 2

, .

date, . DATETIME2.