Somar horas

[Fabiano Carloto 0]

Olá pessoal.

 

Estou com uma dificuldade para adicionar horas no SQL. Tenho um banco com 2 colunas do tipo time, uma com o horário de entrada e a outra com o horário de jornada do funcionário. Preciso saber qual será o horário de saída dele com base na entrada e jornada.

 

Ex.: Funcionário 1

Entrada:08:00:00

Jornada: 06:20:0

Saída(Entrada + Jornada):14:20:00

 

Ex.: Funcionário 2

Entrada:14:20:00

Jornada: 06:20:0

Saída(Entrada + Jornada):20:40:00

 

Ex.: Funcionário 3

Entrada:20:00:00

Jornada: 06:20:0

Saída(Entrada + Jornada):02:20:00 da madrugada

 

E por ai vai.

 

Eu usava no Mysql uma função ADDTIME(Entrada,Saida) e ela já resolvia este calculo pra mim, porem quando migramos para o SQL Server 2008 estou me debatendo com estes problemas.

 

 

Agradeço a todos que poderem ajudar.

[Motta 645]

No Oracle para somar horas a um datetime basta adicionar a fração do dia referente

 

Somar 15 horas a um date time

 

exemplo

 

select (data_inicio + (15/24))
from tabela

[Fabiano Carloto 0]

Opa Motta,

 

O problema é que este horários são bastantes flexíveis. As informações de entrada e jornada para gerar o horário de saída estão em colunas no banco de dados.

[advaldomesquita 93]

Segue alguns exemplos de calculo de hora, incluindo calculo com finais de semana.

Utilizo os scripts abaixo em diversos ambientes..

 

 

Att:

 

 if exists (select 1 from sys.objects where name = 'fn_ContaHoras' and type ='FN')DROP FUNCTION fn_ContaHorasGO CREATE function fn_ContaHoras (@dt_inicio smalldatetime,@dt_final smalldatetime) returns varchar(max)as beginDECLARE @fimdesemana INTDECLARE @i INT SELECT @fimdesemana = 0, @i = 0 WHILE (DATEDIFF(day, @dt_inicio, @dt_final) > @i) beginSELECT @i = @i + 1IF (DATEPART(WEEKDAY, DATEADD(day, @i, @dt_inicio)) in (1,7)) BEGINSELECT @fimdesemana = @fimdesemana + 1ENDEND declare @dia numeric(30),@hora numeric(30), @minuto numeric(30) SELECT @dia =    DATEDIFF(d, @dt_inicio, @dt_final) SELECT @hora =    DATEDIFF(hour, @dt_inicio, @dt_final) select @minuto =  DATEDIFF(MINUTE, @dt_inicio, @dt_final)  if @dia > 0 beginSET @fimdesemana = (@fimdesemana * 8)SET @hora = @hora - (@dia * 24) - @fimdesemanaSET @dia = @dia * 10  end-- return @hora+@diareturn convert(varchar(max),(@hora +@dia)) +':'+convert(varchar(max),(@minuto %60))  endGO   -- select dbo.fn_ContaHoras ('2013-05-28 08:30:00', '2013-05-29 09:00:00')   if exists (select 1 from sys.objects where name = 'FN_CONVMIN' and type ='FN')DROP FUNCTION FN_CONVMINGO CREATE FUNCTION dbo.FN_CONVMIN (@MINUTOS int)RETURNS NVARCHAR(7)BEGIN   DECLARE @iHoras   INTEGER   DECLARE @iMinutos INTEGER    DECLARE @sEdita   VARCHAR(7)      SET @iHoras = CAST(ROUND(@MINUTOS/60, 0) AS INT)   SET @iMinutos = @MINUTOS % 60         SET @sEdita = CASE LEN(@iHoras)                 WHEN 0 THEN '00'                 WHEN 1 THEN '0' + CONVERT(NVARCHAR(1), @iHoras)                 ELSE CONVERT(NVARCHAR(4),@iHoras)                 END    SET @sEdita = @sEdita + ':' + CASE LEN(@iMinutos)                                 WHEN 0 THEN '00'                                  WHEN 1 THEN '0' + CONVERT(NVARCHAR(3), @iMinutos)                                     ELSE CONVERT(NVARCHAR(4), @iMinutos)                                       END    IF @sEdita = '00:00' BEGIN SET @sEdita = '     '  END    RETURN @sEditaENDGO -- select dbo.FN_CONVMIN(28960) if exists (select 1 from sys.objects where name = 'FN_HORA' and type ='FN')DROP FUNCTION FN_HORAGO CREATE FUNCTION FN_HORA (@TEMPO INT) RETURNS VARCHAR(20)ASBEGINDECLARE @HORARIO VARCHAR(20) DECLARE @HORA    INTDECLARE @MINUTO  INTDECLARE @SEGUNDO INT SET @HORA    = (@TEMPO/3600)SET @MINUTO  = (@TEMPO%3600) / 60SET @SEGUNDO = (@TEMPO%3600) % 60  SELECT @HORARIO =    CASE WHEN @TEMPO/3600 >= 1 THEN         CASE LEN(CAST((@HORA)        AS VARCHAR)) WHEN 1 THEN '0' ELSE '' END      + CAST ((@HORA) AS VARCHAR) + ':'       + CASE LEN(CAST((@MINUTO)        AS VARCHAR)) WHEN 1 THEN '0' ELSE '' END      + CAST((@MINUTO) AS VARCHAR) +  ':'       + CASE LEN(CAST((@SEGUNDO)    AS VARCHAR)) WHEN 1 THEN '0' ELSE '' END      + CAST((@SEGUNDO) AS VARCHAR)     ELSE        CASE LEN(CAST((@MINUTO)        AS VARCHAR)) WHEN 1 THEN '0' ELSE '' END      + CAST((@MINUTO) AS VARCHAR) +  ':'       + CASE LEN(CAST((@SEGUNDO)    AS VARCHAR)) WHEN 1 THEN '0' ELSE '' END      + CAST((@SEGUNDO) AS VARCHAR)    END RETURN(@HORARIO)ENDGO -- select dbo.FN_HORA(2658)

 

 

/*ALTER FUNCTION fn_ContaHoras (@inicio datetime, @final datetime) RETURNS varchar(MAX) AS*//***************************************************** CONTA AS HORAS ÚTEIS ENTRE A DATA INICIAL (@INICIO)E A DATA FINAL (@FINAL) *****************************************************/ select dateadd(day,-1,(dateadd(month,1,convert(char(08),getdate(),126)+'01'))) declare @inicio SMALLDATETIMEdeclare @final SMALLDATETIME declare @inicio_dia SMALLDATETIMEdeclare @final_dia SMALLDATETIME BEGINDECLARE @hour VARCHAR(MAX) DECLARE @i INTDECLARE @minutos VARCHAR(300)DECLARE @fimdesemana INT -- DEFINE A DATA Q DEVEIRA TERMINARSELECT @inicio = '2013-05-28 07:00:00.000'-- DEFINE A DATA Q TERMINOU DE FATOSELECT @final = '2013-06-01 10:00:00.000'  -- select @inicio = CONVERT(varchar(12), @inicio, 112)-- select @inicio + '18:00:00' SELECT @inicio_dia = CONVERT(varchar(12), @inicio, 112) SELECT @inicio_dia = (@inicio_dia  + '18:00:00' )   SELECT @final_dia = CONVERT(varchar(12), @final, 112) SELECT @final_dia = (@final_dia  + '08:00:00' )   -- CONTA OS DIAS UTEIS ENTRE AS DATASSELECT @fimdesemana = 0, @i = 0 WHILE (DATEDIFF(day, @inicio, @final) > @i) beginSELECT @i = @i + 1IF (DATEPART(WEEKDAY, DATEADD(day, @i, @inicio)) in (1,7)) BEGINSELECT @fimdesemana = @fimdesemana + 1ENDEND declare @horainicio intdeclare @horafim intdeclare @horadias int select  (datediff(MINUTE,@inicio, @inicio_dia)/60)select (datediff(MINUTE,@final_dia, @final)/60)select @fimdesemana  return SELECT (DATEDIFF(HOUR,@inicio, @final) - (@fimdesemana * 24)  ) as calcEND 
 

 

TIPO DE DATAS, Caso precise:

SELECT CONVERT(VARCHAR(12),GETDATE(),101) AS '101', --mm/dd/aaaaCONVERT(VARCHAR(12),GETDATE(),102) AS '102', --aa.mm.ddCONVERT(VARCHAR(12),GETDATE(),103) AS '103', --dd/mm/aaaaCONVERT(VARCHAR(12),GETDATE(),104) AS '104', --dd.mm.aaCONVERT(VARCHAR(12),GETDATE(),105) AS '105', --dd-mm-aaCONVERT(VARCHAR(12),GETDATE(),106) AS '106', --dd mês aaCONVERT(VARCHAR(12),GETDATE(),107) AS '107', --Mês dd, aaCONVERT(VARCHAR(12),GETDATE(),108) AS '108', --hh:mi:ssCONVERT(VARCHAR(12),GETDATE(),109) AS '109', --mês dd aaaa hh:mi:ss:mmmAM (ou PM)CONVERT(VARCHAR(12),GETDATE(),110) AS '110', --mm-dd-aaCONVERT(VARCHAR(12),GETDATE(),111) AS '111', --aa/mm/ddCONVERT(VARCHAR(12),GETDATE(),112) AS '112', --aammddCONVERT(VARCHAR(12),GETDATE(),113) AS '113', --dd mês aaaa hh:mi:ss:mmm (24h)CONVERT(VARCHAR(12),GETDATE(),114) AS '114', --hh:mi:ss:mmm(24h)CONVERT(VARCHAR(12),GETDATE(),120) AS '120', --aaaa-mm-dd hh:mi:ss(24h)CONVERT(VARCHAR(12),GETDATE(),121) AS '121' --aaaa-mm-dd hh:mi:ss.mmm(24h)
 

 declare @tabela table (nome varchar(1000), telefone varchar(20), sexo varchar(1), data_nascimento datetime) insert into @tabela values ('nome_01', '(11)99999-9999', 'M','1980-01-01')insert into @tabela values ('nome_02', '(11)88888-8888', 'F','1990-02-11')insert into @tabela values ('nome_03', '(11)77777-7777', 'F','1976-03-21')insert into @tabela values ('nome_04', '(11)66666-6666', 'M','1965-04-15')insert into @tabela values ('nome_05', '(11)55555-5555', 'F','1986-05-16')insert into @tabela values ('nome_06', '(11)44444-4444', 'M','1800-06-17')  --, datediff(yy, data_nascimento, getdate()) as Idade -- Em anos--, datediff(mm, data_nascimento, getdate()) as Meses -- Em meses SELECT *  -- TODOS OS REGISTROS, datediff(yy, data_nascimento, getdate())FROM @tabela-- WHERE -- sexo = 'F' -- SOMENTE SEXO FEMININO--AND  datediff(yy, data_nascimento, getdate()) BETWEEN 20 AND 30 -- IDADE ENTRE 20 E 30 ANOS   declare @t table (nome varchar(60), data datetime)insert into @t values ('Gustavo Junqueira Dorta','20090405 08:30:00')insert into @t values ('Gustavo Junqueira Dorta','20090505 09:00:00')insert into @t values ('Gustavo Junqueira Dorta','20090605 08:40:00')insert into @t values ('Gustavo Junqueira Dorta','20090705 08:49:00')insert into @t values ('Gustavo Junqueira Dorta','20090805 08:30:01');with DifSegs as (select nome, data, datediff(ss,convert(char(8),data,112),data) as segundos from @t)select Nome, SUM(Segundos) / 3600 As Horas, (SUM(Segundos) % 3600) / 60 As Minutos, (SUM(Segundos) % 3600) % 60 As Segundos  from DifSegsGROUP BY Nome 

[Fabiano Carloto 0]

Olá A.Jr,

 

Cara olhando seus scripts vi um que com certeza vou utilizar em uma outra consulta que tenho mais eu preciso que algo que some somente as horas da tabela. Coluna entrada + Coluna jornada = saída como nos exemplos acima. Eu ainda sou meio leigo em SQL Server pois usava o Mysql.

 

Mesmo assim muito obrigado.

[advaldomesquita 93]

No SQL temos DATEADD, DATEDIFF que podem fazer calculos com data.Qual sua versão do SQL? 2012 correto?

Eu não sei o formato do campo, mas se seu db estiver como postou, soma-se direto:

 

DECLARE @dt_inicio datetimeDECLARE @dt_final datetimeDECLARE @jornada datetime set @dt_inicio = '14:20:00'  set @dt_final = '06:20:00'   select convert(varchar(10),(@dt_final + @dt_inicio),114) 

Seu campo nao pode ser do tipo VARCHAR,NCHAR, TEXT ou qq outro com tipo texto.

Att.

fonte:

http://forum.imasters.com.br/topic/318757-resolvidosomar-horas/

[Fabiano Carloto 0]

Olá A.Jr,

 

Olhando seu ultimo post, tentei executar da mesma forma que você mostrou, no entanto os campos entrada e jornada estão com time(0). Ai tentei executar como no seu exemplo e apresentou o erro "Operand data type time is invalid for add operator." Ja tinha conseguido fazer uma serie de case when com DATEPART, funcionou mais ficou muito grande. Ai eu apenas utilizei o CONVERT, como abaixo, e funcionou perfeitamente. Minha versão de SQL Server é a 2008.

select convert(varchar(10),(convert(datetime,tab_go_cad_cursos.entrada) + convert(datetime,tab_go_cad_cursos.jornada)),114) from tab_go_cad_cursos;

Com isso, ele calculou perfeitamente.

 

 

Muito obrigado A.Jr pela atenção e ajuda.

[advaldomesquita 93]

:clap:

Que bom!!!

 

Precisando , só voltar a postar

 

Boa sorte

 

Att.

:coolio: