By: Jim Evans | Actualizado: 2020-07-16 | Comentários (1) | Relacionados: Mais > Datas
Problema
Muitas vezes, precisamos de calcular a diferença entre duas datas e devolver os resultados numa parte ou incremento de data desejada, como dias, horas, minutos. Felizmente, o SQLServer fornece uma função para isto.
Neste artigo, irei demonstrar as funções SQL DATEDIFF e DATEDIFF_BIG e partilhar exemplos gerais de como usar cada uma delas. Mostrar-vos-ei também as limitações e como trabalhar em torno delas. Também aprenderei como calcular a idade da cidade de Santo Agostinho em nanossegundos.
Solução
Exploraremos as funções DATEDIFF e DATEDIFF_BIG, mostraremos como são usadas, e forneceremos vários exemplos.
O que é a função SQL Server DATEDIFF
DATEDIFF() é uma função SQL Server básica que pode ser usada para fazer matemática de datas. Especificamente, obtém a diferença entre 2 datas com os resultados devolvidos em unidades de data especificadas como anos, meses dias, minutos, segundos como um valor int (inteiro).
Sintax:
DATEDIFF( DatePart, StartDate, EndDate )
O que é a função SQL Server DATEDIFF_BIG
DATEDIFF_BIG() é uma função SQL que foi introduzida no SQL Server 2016. Também pode ser utilizada para fazer contas de datas. Especificamente, obtém a diferença entre 2 datas com os resultados devolvidos em unidades de data especificadas como anos, meses dias, minutos,segundos como um valor bigint.
Sintax:
DATEDIFF_BIG( DatePart, StartDate, EndDate )
Como usar DATEDIFF e DATEDIFF_BIG
O uso de DATEDIFF e DATEDIFF_BIG são os mesmos: DATEDIFF(data parte dos valores de retorno, data de início, data de fim).
Exemplos:
SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000 SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000
Primeiro Parâmetro: é um documento válido de parte de data que é um dos seguintes de nanossegundo a ano:
| DatePart Name | |
|---|---|
| year | yyy, yyyy |
| quarter | qq, q |
| mês | mm, m |
| dy, y | |
| day | dd, d |
| wk, ww | |
| hora | |
| mi, n | |
| segundo | ss, s |
| millisecond | ms |
| mcs | |
| ns |
Parâmetro de segundo: é a Data de Início. Uma data válida, datatime, datatimeoffset, datatime2, datatime2, datatime pequena, ou tipo de dados de horavariável ou uma string que resolveu para um datatype de datatime.
Terceiro Parâmetro: é a Data de Fim. Uma data válida, datetime, datetimeoffset, datetime2, smallldatetime, ou uma variável de tipo de dados de hora ou uma string que resolveu para um datatype.
*Esta informação pode ser encontrada em Microsoft Docs!
SQL Server DATEDIFF Exemplos
Below são exemplos básicos usando os argumentos de datapart mais comuns.
| Exemplo | ||
|---|---|---|
| Quantos minutos num dia? | SELECT DATEDIFF(MINUTO, ’07-04-2020′, ’07-05-2020′) | 1440 minutos |
| Quantas horas num dia? | SELECT DATEDIFF(HOUR, ’01-01-2020′, ’01-02-2020′) | 24 Horas |
| Quantos dias num ano? | SELECT DATEDIFF(DIA, ’01-01-2020′, ’12-31-2020′) | 365 Dias |
| Quantos meses num ano? | SELECT DATEDIFF(MÊS, ’01-01-2019′, ’01-01-2020′) | 12 Meses |
| Quantos anos de 2000 a 2020? | SELECT DATEDIFF(ANO, ’01-01-2000′, 01-01-2020′) | 20 Anos |
Quando utilizar a função DATEDIFF_BIG do SQL Server
Utilizar a função DATEDIFF_BIG quando os seus resultados excederem o intervalo de um valor inteiro que se situa entre (-2.147.483.648 a +2.147.483.647).
BIGINT tem um intervalo de (-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807). Parece que seria difícil exceder este intervalo! Mas vamos apresentar um exemplo que ultrapassa este intervalo quando se usa DATEDIFF_BIG.
SQL Server DATEDIFF_BIG em vez de DATEDIFF
Como ultrapassar o valor de retorno DATEDIFF int? Microsoft Docs fornece 2 exemplos de como ultrapassar o valor de retorno inteiro DATEDIFF que demonstro abaixo e mostro como DATEDIFF_BIG funciona para contornar a limitação.
--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. -- the following will exceed this range.SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020')
Isto resulta no seguinte erro.
A função datediff resultou num sobrefluxo. O número de peças de data separando duas instâncias de data/hora é a carga de ferramenta. Tente usar datiff com uma dataparte menos precisa.
Aqui está outro exemplo.
--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. -- the following will exceed this range.SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020')
Isto resulta no seguinte erro.
A função datiff resultou num overflow. O número de peças de data separando duas instâncias de data/hora é a carga de ferramenta. Tente usar datediff com uma parte de data menos precisa.
Utilizar DATEDIFF_BIG em vez disso para evitar os erros acima mencionados.
--3. How to get around the integer limit using DATEDIFF_BIG functionSELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200
Quando DATEDIFF_BIG não é suficientemente grande
De acordo com Microsoft Docs, DATEDIFF_BIG só pode transbordar se usar nanossegundo critério onde a diferença entre data final e data de início é superior a 292 anos,3 meses, 10 dias, 23 horas, 47 minutos e 16.8547758 segundos.
Esta pode ser uma questão se quiser saber quantos nanossegundos tem sido desde a fundação da cidade mais antiga das Américas, St. Augustine Florida!
--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())
Bummer! Recebemos um erro.
A função datediff_big resultou num transbordamento. O número de peças de data separando duas instâncias de data/hora é a carga de ferramenta. Tente usar datiff_big com uma dataparte menos precisa.
Vamos tentar isto em múltiplos passos:
--1. First get the number of Years since St. Augustine was foundedSELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455 --2. Next, get the number of nanoseconds in a yearSELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900--3. Last multiply the results together and cast to decimal(38,0)-- 455 x 31622399999999900 = 14388191999999954500SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500
Walla! Agora sabemos quantos nanossegundos desde a fundação de Santo Agostinho.
Wrap Up
Espero que tenha gostado deste exercício em DATEDIFF e DATEDIFF_BIG. DATEDIFF é a função SQL utilizada habitualmente há muitos anos. DATEDIFF_BIG é uma função relativamente nova que foi introduzida no SQL Server 2016. Embora eu tenha fornecido exemplos de transbordamento destas funções, é raro encontrar estes cenários. Estas são funções úteis que devem ser adicionadas à sua caixa de ferramentas SQL!
Passos seguintes
- Ir mais a fundo com – outros artigos Datediff
- Leia acerca de -trabalho para calcular a idade
- Leia acerca de outras funções SQL interessantes -LAG e LEAD
- Inconte outros artigos em Datetime -MS SQL Tips Search on DateDiff
br>Última Actualização: 2020-07-16
h5>Sobre o autor
Jim Evans é actualmente um Gestor de TI com a Harsco que tem gerido DBAs, Desenvolvedores de aplicações e BI e equipas de gestão de dados há mais de 20 anos.Ver todas as minhas dicas
ul>>li>Mais Dicas de Desenvolvedores de Bases de Dados…
br