Link to home
Start Free TrialLog in
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..
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

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

Greg


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

=DateAdd("n",-YourDurationMinsField,YourDisconnectionDateField)
Avatar of 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)
Avatar of serg2626
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
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)
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

ASKER CERTIFIED SOLUTION
Avatar of edlunad
edlunad
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Depending on the field type of the table, one of my expressions posted in a36316913 should give the user what he needs
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.