How do I subtract the remaining hours from an event:
Example at 3 PM, which would be 3:00 p.m. I have to go to the gym, I have the time I have to go in this format:
'2021-03-02 10:00:00'
How many hours do I have left if I currently have '2021-02-28 10:00:00' I have this Script but it doesn't work as I want:
DECLARE @tiemporeal int
SELECT TOP 1 [ActionTime] FROM [PS_GameLog].[dbo].[Gym])
SET @tiemporeal =(SELECT TOP 1 (1-(
DATEPART(SECOND, [ActionTime]) +
1 * DATEPART(MINUTE, [ActionTime]) +
3600 * DATEPART(HOUR, [ActionTime] )
)) AS Resta_1
--,
--(SUM(
--DATEDIFF(SECOND,0,cast(Duracion AS DATETIME))
--)))) AS Resta_2
FROM [PS_GameLog].[dbo].[Gym])
SELECT (CAST(DATEADD(SECOND, @tiemporeal, 0) AS TIME))as Restante
I want it in this format: '71:59:59' any ideas? How to get the format and the subtraction? It should be countdown but I don't get an example
'71:59:59'
'71:59:58'
'71:59:57'
'71:59:56'
So until you reach zero.
Starting from the idea that you have two dates and you want to show the missing time in a formatted string
'hh:mm:ss'
, you can useTo assemble a string containing this information of hours, minutes and seconds in the desired format.
The basic idea, step by step, is expressed in the following script:
Which produces the result:
Which, of course, can be boiled down to a small query that does all the work at once, for example:
If you have the information in a table and you want to know how much time is left, from the current time, the sentence would be something like:
Edition
Since the AP uses an old version of SQL Server, there is no support for the format function, so I will change the code in the last statement to exemplify how it can be done with this version:
I have left the number of hours with a length of 3, as a precaution, as the above solution would display all significant digits if they are more than 2, while this one would cut the string by displaying only the last 3 digits for any number greater than 999 hours. .