Cálculo Datas SQL SERVER

Vejo sempre em fóruns muitos usuários com dúvidas sobre cálculos com datas, então resolvi postar alguns exemplos deste cálculo utilizando as funções DATEDIFF, DATEPART e DATEWEEK.
As funções funcionam desde a versão SQL2005. Na versão 2012 temos outras que devem ser material de um próximo tópico.
Em todos os exemplos, estou supondo que o campo de data esta como DATETIME ou SMALLDATETIME e a language como english.

Primeiro vamos conhecer os tipos de datas convertidas

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT CONVERT(VARCHAR(12),GETDATE(),101) AS '101', --mm/dd/aaaa CONVERT(VARCHAR(12),GETDATE(),102) AS '102', --aa.mm.dd CONVERT(VARCHAR(12),GETDATE(),103) AS '103', --dd/mm/aaaa CONVERT(VARCHAR(12),GETDATE(),104) AS '104', --dd.mm.aa CONVERT(VARCHAR(12),GETDATE(),105) AS '105', --dd-mm-aa CONVERT(VARCHAR(12),GETDATE(),106) AS '106', --dd mês aa CONVERT(VARCHAR(12),GETDATE(),107) AS '107', --Mês dd, aa CONVERT(VARCHAR(12),GETDATE(),108) AS '108', --hh:mi:ss CONVERT(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-aa CONVERT(VARCHAR(12),GETDATE(),111) AS '111', --aa/mm/dd CONVERT(VARCHAR(12),GETDATE(),112) AS '112', --aammdd CONVERT(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)

Agora um cálculo básico: Cálculo de idade
Para cálculo de idade, utilizo a função DATEDIFF.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

/* TABELA COM DADOS FICTIOS */ declare @tabela table (nome varchar(10),  data_nascimento datetime)   insert into @tabela values ('nome_01', '1980-01-01') insert into @tabela values ('nome_02', '1990-02-11') insert into @tabela values ('nome_03', '1976-03-21') insert into @tabela values ('nome_04', '1965-04-15') insert into @tabela values ('nome_05', '1986-05-16') insert into @tabela values ('nome_06', '1800-06-17')         SELECT     *  -- TODOS OS REGISTROS     , datediff(yy, data_nascimento, getdate()) as idade -- parametro YY ou YEAR para mostrar o resultado em ANOS. FROM @tabela

–Results
nome data_nascimento idade

---

nome_01 1980-01-01 00:00:00.000 34
nome_02 1990-02-11 00:00:00.000 24
nome_03 1976-03-21 00:00:00.000 38
nome_04 1965-04-15 00:00:00.000 49
nome_05 1986-05-16 00:00:00.000 28
nome_06 1800-06-17 00:00:00.000 214

Para ficar mais interessante: Que dia da semana o meu usuário nasceu?
Função: DATEPART com o parametro WEEKDAY

1 2 3 4 5 6 7

-- DIA 07/06/2014 vai cair que dia da semana? SELECT DATEPART(WEEKDAY,'2014-06-07')     -- Results -----------    7

Mas o que siginifica este 7?
O DATEPART tem como retorno um campo INT, com isso o número dos dias da semana fica assim:
DOMINGO = 1
SEGUNDA =2
TERCA = 3
QUARTA = 4
QUINTA = 5
SEXTA = 6
SABADO = 7
Portanto dia 07/06/2014 será um Sábado.

Porém fica complicado usar um CASE para cada dia da semana, então agora vamos usar o DATENAME que tem como retorno o dia da semana por extenso:

1 2 3 4 5

SELECT DATENAME(WEEKDAY,'2014-06-07')   -- Results ------------------------------ Saturday

Simples?! Agora vamos testar as funções com a seguinte situação:
Fechamento de horas de cartão de ponto, onde eu tenha que contar somente os dias da semana (segunda a sexta)
Neste caso, não temos tabelas com feriados, portanto vale de segunda a sexta.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

/***************************************************** CONTA AS HORAS ÚTEIS ENTRE A DATA INICIAL (@INICIO) E A DATA FINAL (@FINAL) *****************************************************/     CREATE function fn_HorasUteis (     @dt_inicio smalldatetime,     @dt_final smalldatetime )   returns varchar(max) as   begin DECLARE @fimdesemana INT DECLARE @i INT   /**** CALCULO DE FINAL DE SEMANA ****/ SELECT @fimdesemana = 0, @i = 0 WHILE (DATEDIFF(day, @dt_inicio, @dt_final) > @i) BEGIN     SELECT @i = @i + 1     IF (DATEPART(WEEKDAY, DATEADD(day, @i, @dt_inicio)) in (1,7))         BEGIN         SELECT @fimdesemana = @fimdesemana + 1         END END     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     begin         SET @fimdesemana = (@fimdesemana * 8)         SET @hora = @hora - (@dia * 24) - @fimdesemana         SET @dia = @dia * 10         end   return convert(varchar(max),(@hora +@dia)) +':'+convert(varchar(max),(@minuto %60))   end

Exemplo de uso:
SELECT dbo.fn_HorasUteis(‘2013-01-29 08:00:00′,’2013-01-29 18:00’)

— Results

10:00

Podemos então alterar nossa primeira tabela para trazer o dia da semana em que o usuário nasceu:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

/* TABELA COM DADOS FICTIOS */ declare @tabela table (nome varchar(10),  data_nascimento datetime)   insert into @tabela values ('nome_01', '1980-01-01') insert into @tabela values ('nome_02', '1990-02-11') insert into @tabela values ('nome_03', '1976-03-21') insert into @tabela values ('nome_04', '1965-04-15') insert into @tabela values ('nome_05', '1986-05-16') insert into @tabela values ('nome_06', '1800-06-17')         SELECT     *  -- TODOS OS REGISTROS     , datediff(yy, data_nascimento, getdate()) as idade -- parametro YY ou YEAR para mostrar o resultado em ANOS.     , DATENAME(weekday,data_nascimento) as [dia_semana] FROM @tabela

–Results
nome data_nascimento idade dia_semana

---

nome_01 1980-01-01 00:00:00.000 34 Tuesday
nome_02 1990-02-11 00:00:00.000 24 Sunday
nome_03 1976-03-21 00:00:00.000 38 Sunday
nome_04 1965-04-15 00:00:00.000 49 Thursday
nome_05 1986-05-16 00:00:00.000 28 Friday
nome_06 1800-06-17 00:00:00.000 214 Tuesday