Avatar of serg2626
serg2626
 asked on

Subtract Minutes from a Time Format in SQL Report Expression

Hello,
I currently have a field that holds the duration of a VPN connection. Its looks like this "30" in the field. I have another field that holds the VPN disconnect time, which looks like this "11:30:00:" I need to come up with the connection time to the VPN. I created a new field on the SQL Report that will hold the connect time but need to know the expression I can use to get this. For example, the disconnect was at 11:30am. If I subtract the duration which was 30 minutes, the Connect Time field should show 11:00:00:.

How can I accomplish this?

Thank you..
Microsoft SQL ServerSSRSMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
JestersGrind

SELECT DATEADD(MI, - @DurationInMinutes, @DisconnectTime)

Greg


edlunad

Try this, just replace it with your fields...

=DateAdd("n",-YourDurationMinsField,YourDisconnectionDateField)
ukerandi

Check this one its work for me

SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-@DurationMinutes,@Yourdisconnecttime),114),8)

SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-30,'11:30:00'),114),8)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
serg2626

ASKER
edlunad,
I get an error when trying your expression. What is the "n" for?

Please keep in mind that the information in the Duration field always changes depending on the amount of time the user is connected to the VPN.

Thank you
edlunad

n is for minutes, that should not be causing the error on your expression, did you replace the fields with the ones of your datasource? should look more like this...

=DateAdd("n",-Fields!YourDurationMinsField,Fields!YourDisconnectionTimeField)
serg2626

ASKER
Yes, I did.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JestersGrind

My example above is if you wanted to do the calculation in the data source.  If you want to do it in the report, it would look like this:

=DATEADD(MINUTE, -Fields!Duration, Fields!DisconnectionTime)

Greg

ASKER CERTIFIED SOLUTION
edlunad

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
edlunad

Depending on the field type of the table, one of my expressions posted in a36316913 should give the user what he needs
Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck