Subtract Minutes from a Time Format in SQL Report Expression

serg2626
serg2626 used Ask the Experts™
on
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..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT DATEADD(MI, - @DurationInMinutes, @DisconnectTime)

Greg


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

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

Commented:
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)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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

Commented:
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)

Author

Commented:
Yes, I did.
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

Commented:
What are the types of your fields that I called  "MyDurationMins" and "MyDisconnectionTime"?, I assuming that the first is integer and the second datetime, but if they are not you should perform a conversion before calculation..

i.e. if your first field is a string and the second one datetime, your expression should look like this...

=DateAdd("n",-1*CInt(Fields!YourDurationMinsField),Fields!YourDisconnectionTimeField)

or if both are string, you should do it like this...

=DateAdd("n",-1*CInt(Fields!YourDurationMinsField),CDate(Fields!YourDisconnectionTimeField))

Commented:
Depending on the field type of the table, one of my expressions posted in a36316913 should give the user what he needs
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial