how do i make -negative minutes in a time not be negative but positive

Question

I have a SQL query that I'm trying to find the time difference between two date fields. I need to have min column positive numbers otherwise the user would get confused.

My query

SELECT
tblMove.intProp,
tblCustomerSpaces.strSpace,
tblCustomerSpaces.intCustSpace AS intCustSpaceMoveOut,
CAST(SWITCHOFFSET(tblMove.dtMove, DATENAME(tz, tblMove.dtMove)) as datetime) AS dtMoveOut,
tblCustomer.strLName AS strMOLast,
tblListMoveOutReasons.strReason,
qryNextMoveIn.dtMoveIn,
qryNextMoveIn.intCustSpaceMoveIn,
qryNextMoveIn.strLName AS strMILast,
tblMove.fltSpaceTypeOcc,

DATEDIFF(hh, CAST(SWITCHOFFSET(tblMove.dtMove, DATENAME(tz, tblMove.dtMove)) as datetime), qryNextMoveIn.dtMoveIn) AS intHours,
DATEPART(MINUTE,tblMove.dtMove) - DATEPART(MINUTE,qryNextMoveIn.dtMoveIn)as 'minutes difference',



            
qryNextMoveIn.strDescription
FROM
tblMove WITH (NOLOCK)
INNER JOIN tblCustomerSpaces WITH (NOLOCK) ON tblMove.intProp = tblCustomerSpaces.intProp AND tblMove.intCustSpace = tblCustomerSpaces.intCustSpace
INNER JOIN tblCustomer WITH (NOLOCK) ON tblCustomerSpaces.intProp = tblCustomer.intProp AND tblCustomerSpaces.intCustNumb = tblCustomer.intCustNumb
Inner join tblListMoveOutReasons WITH (NOLOCK) 
ON tblMove.intMOReason = tblListMoveOutReasons.intReason

Answer 1

If you want the returned values in the column [minutes difference] to be positive, you can use the function ABS().

ABS(DATEDIFF(hh, CAST(SWITCHOFFSET(tblMove.dtMove, DATENAME(tz, tblMove.dtMove)) as datetime), qryNextMoveIn.dtMoveIn) AS intHours, DATEPART(MINUTE,tblMove.dtMove) - DATEPART(MINUTE,qryNextMoveIn.dtMoveIn)) AS [minutes difference]

Answer 2

Hi @Jannette Jones

The graph you're showing is a bit blurry, and I'm guessing you're trying to make the 'minutes difference' fields all positive.

You can use the case when statement to determine the size and subtract the small value from the large value.

Just like this.

case when DATEPART(MINUTE,tblMove.dtMove) >= DATEPART(MINUTE,qryNextMoveIn.dtMoveIn) 
    then DATEPART(MINUTE,tblMove.dtMove) - DATEPART(MINUTE,qryNextMoveIn.dtMoveIn)
else DATEPART(MINUTE,qryNextMoveIn.dtMoveIn) - DATEPART(MINUTE,tblMove.dtMove) end as 'minutes difference'

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.