how to add minutes to my hours?

hidrau
hidrau used Ask the Experts™
on
Hello guys

I have this code:

declare @start_time time;
declare @elapse_time time;

set @start_time =  '13:30:00'
Set @elapse_time= '00:30:00'

SELECT DATEADD(mi, @start_time, @elapse_time)

Open in new window


How is the correct form?

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
The DATEADD function requires an integer for the 2nd argument so if you want to add 30 minutes to a time, this is how it should be done:
declare @start_time time;
declare @elapse_time int;

set @start_time =  '13:30:00'
Set @elapse_time= 30

SELECT DATEADD(mi, @elapse_time, @start_time)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Please use like below -

Full and tested solution for you. :)

declare @start_time time;
declare @elapse_time time;

set @start_time =  '13:30:00'
Set @elapse_time= '00:30:00'

SELECT DATEADD(SECOND,DATEDIFF(SECOND,'00:00:00',@start_time),@elapse_time) 
AS finalTime

Open in new window


OUTPUT


/*------------------------

declare @start_time time;
declare @elapse_time time;

set @start_time =  '13:30:00'
Set @elapse_time= '00:30:00'

SELECT DATEADD(SECOND,DATEDIFF(SECOND,'00:00:00',@start_time),@elapse_time) 
AS finalTime
------------------------*/
finalTime
----------------
14:00:00.0000000

(1 row(s) affected)

Open in new window

Author

Commented:
ok, I understood.

If I have this value  '01:10', I should convert the value to integer and add to my dateadd.

In this case, '01:10' how could I convert the value to integer in a correct way so the result would be 70?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
In that case it will be 14:40

No need to convert to INT. the function dateadd will automatically handle it.

/*------------------------

declare @start_time time;
declare @elapse_time time;

set @start_time =  '13:30:00'
Set @elapse_time= '01:10:00'

SELECT DATEADD(SECOND,DATEDIFF(SECOND,'00:00:00',@start_time),@elapse_time) AS finalTime
------------------------*/
finalTime
----------------
14:40:00.0000000

(1 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
edited my last comment.

Author

Commented:
Pawan,

declare @PREVISAO_INICIO datetime;
declare @TEMPO_ESTIMADO  varchar(10);

set @PREVISAO_INICIO = '20171101 13:00:00'
set @TEMPO_ESTIMADO  = '001 00:30:00'   -- ddd hh:mm:ss


how to make this calc in MSSQL
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Pls use this

/*------------------------

declare @PREVISAO_INICIO datetime;
declare @TEMPO_ESTIMADO  varchar(50);

set @PREVISAO_INICIO = '20171101 13:00:00'
set @TEMPO_ESTIMADO  = '001 00:30:00'   -- ddd hh:mm:ss

SELECT 

DATEADD(DAY , CAST(SUBSTRING(@TEMPO_ESTIMADO,0,CHARINDEX(' ',@TEMPO_ESTIMADO,0)) AS INT),   @PREVISAO_INICIO )
 +  SUBSTRING(@TEMPO_ESTIMADO,CHARINDEX(' ',@TEMPO_ESTIMADO,0)+1, LEN(@TEMPO_ESTIMADO))
------------------------*/

-----------------------
2017-11-02 13:30:00.000

(1 row(s) affected)

Open in new window

Author

Commented:
Pawan, you helped me a lot.

Thank you very very much

Author

Commented:
thanks a lot

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