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


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

tblCustomerSpaces.intCustSpace AS intCustSpaceMoveOut,
CAST(SWITCHOFFSET(tblMove.dtMove, DATENAME(tz, tblMove.dtMove)) as datetime) AS dtMoveOut,
tblCustomer.strLName AS strMOLast,
qryNextMoveIn.strLName AS strMILast,

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',

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

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,696 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    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]
    0 comments No comments

  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

    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.

    0 comments No comments