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..
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..
Try this, just replace it with your fields...
=DateAdd("n",-YourDuration MinsField, YourDiscon nectionDat eField)
=DateAdd("n",-YourDuration
Check this one its work for me
SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-@DurationMinutes,@Yourdi sconnectti me),114),8 )
SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-30,'11:30:00'),114),8)
SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-@DurationMinutes,@Yourdi
SELECT Left(CONVERT (VARCHAR(12), DATEADD (MINUTE ,-30,'11:30:00'),114),8)
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
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!YourD urationMin sField,Fie lds!YourDi sconnectio nTimeField )
=DateAdd("n",-Fields!YourD
ASKER
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
=DATEADD(MINUTE, -Fields!Duration, Fields!DisconnectionTime)
Greg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Greg