Share via

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

Anonymous
2023-03-09T17:59:12.8533333+00:00

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

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.
13,365 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,201 Reputation points
    2023-03-09T18:33:04.2666667+00:00

    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
  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-03-10T01:36:44.5466667+00:00

    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