• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 50
  • Last Modified:

how to add minutes to my hours?

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
0
hidrau
Asked:
hidrau
  • 4
  • 4
3 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
hidrauAuthor 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?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
edited my last comment.
0
 
hidrauAuthor 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
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
hidrauAuthor Commented:
Pawan, you helped me a lot.

Thank you very very much
0
 
hidrauAuthor Commented:
thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now