Como obter o primeiro dia da semana no SQL Server

Este é um conteúdo adicional para o curso LearnSQL.com.br Análise do Comportamento do Cliente no SQL Server.

No artigo anterior, falamos sobre como você pode usar a função do SQL Server DATEPART() com semana ou iso_week para agrupar eventos por semana. A consulta pode se parecer com isto:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

O resultado é algo parecido com isto:

A consulta exibe o número da semana como o rótulo para a semana. Este rótulo não é muito útil. Afinal de contas, como sabemos o que significa "semana 22"? Será em abril, maio ou junho? É melhor exibir uma data associada a cada semana, ou seja, seu primeiro dia.

Neste artigo, mostraremos a você como obter o primeiro dia da semana no SQL Server. Primeiro acima: um atalho.

O Hack: Usando a função MIN()

Antes de discutirmos as formas adequadas de calcular o primeiro dia da semana no SQL Server, vamos falar sobre um truque que você pode usar para mostrar um rótulo legível durante uma semana - a função MIN():

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  MIN(RegistrationDate) as WeekStart,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

O resultado será o seguinte:

Esta consulta mostra o número e a data mínima de registro de cada semana (em outras palavras, o primeiro dia da semana). É claro que esta solução não é de forma alguma perfeita; ela assume que os eventos que você está contando (neste caso, os registros) ocorrem todos os dias. Se não houver inscrições no primeiro dia da semana, a consulta lhe mostrará o segundo dia da semana como o rótulo WeekStart.

Esta solução pode ser boa o suficiente se você estiver simplesmente trabalhando interativamente com dados e precisar de uma aproximação de quando um evento aconteceu. Entretanto, se você precisar da data precisa para o primeiro dia da semana, isto não será suficiente. Vamos tentar algo mais.

Como calcular os rótulos da Semana no SQL Server

Há duas maneiras comuns de definir o primeiro dia da semana: Domingo (normalmente usado nos EUA) e segunda-feira (normalmente usado na Europa). Começaremos com uma discussão sobre como você pode encontrar o primeiro dia da semana em qualquer um desses estilos.

Opção 1: Domingo como o primeiro dia da semana

Começaremos com o domingo, já que é mais fácil de explicar. Aqui está a expressão:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday;

A função DATEADD() leva três argumentos: uma parte de data, um número, e uma data. Em seguida, adiciona um valor numérico especificado à parte da data especificada de um valor de data de entrada e, posteriormente, retorna esse valor modificado.

Na expressão acima, adicionamos um número especificado de semanas à data -1. O que isso significa? Bem, a data 0 é meia-noite de 1º de janeiro de 1900, que por acaso é segunda-feira. Portanto, a data -1 é domingo, 31 de dezembro de 1899. A expressão acima acrescenta um certo número de semanas a esta data.

O argumento do número em nossa expressão é computado usando a função DATEDIFF(). DATEDIFF() também leva três argumentos: a parte da data, a data de início e a data final. Ela retorna a contagem das partes de data especificadas entre a data de início e a data final. Quando usado com o argumento da semana, DATEDIFF() funciona independentemente da configuração DATEFIRST: ele sempre usa o domingo como primeiro dia da semana.

A expressão DATEDIFF(week, -1, RegistrationDate) calcula o número de semanas entre domingo, 31 de dezembro de 1899 (a data -1) e o RegistrationDate.

Esta expressão:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday

... leva o número de semanas entre domingo, 31 de dezembro de 1899 e a data de registro, acrescenta esse número a esse domingo, e finalmente retorna domingo (em outras palavras, o início da semana em que ocorreu o registro).

Opção 2: Segunda-feira como o primeiro dia da semana

Agora, vamos dar uma olhada em uma expressão que retorna segunda-feira como o primeiro dia da semana:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

Na expressão acima, adicionamos o número especificado de semanas à data 0. Como você se lembra, 0 representa a meia-noite de segunda-feira, 1º de janeiro de 1900.

A função DATEDIFF() trata o domingo como o primeiro dia da semana, independentemente da configuração DATEFIRST. Este é o calendário para janeiro de 1900 que o DATEDIFF() utiliza:

A expressão DATEDIFF(week, 0, RegistrationDate - 1) calcula o número de semanas entre segunda-feira, 1º de janeiro de 1900 (data 0) e um dia antes da data de registro. O turno de um dia para trás é necessário porque DATEDIFF() usa o domingo como o primeiro dia da semana.

Vejamos o domingo, 7 de janeiro de 1900, como um exemplo. A expressão DATEDIFF(week, 0, '19000107') retornará 1; se você tratar o domingo como o primeiro dia da semana, o 7 de janeiro está na semana 2. Entretanto, gostaríamos que o 7 de janeiro fosse tratado como se estivesse na semana 1 - como se a segunda-feira fosse o primeiro dia da semana. Portanto, temos que "voltar" em um dia para obter o número certo de semanas para uma data.

Em última análise, esta é a expressão que recebemos:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

BÔNUS: Como calcular o primeiro dia da semana, dependendo do cenário DATEFIRST

Se você quiser ver uma expressão que funcione corretamente para todos os ambientes DATEFIRST, tenho algumas más notícias para você: não é bonito. Na prática, você provavelmente considerará apenas a segunda-feira ou o domingo como o primeiro dia da semana. Mas, para a completude deste artigo, aqui está uma expressão que funcionará corretamente, independentemente do DATEFIRST:

SELECT DATEADD(week, 
  DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate-(@@DATEFIRST % 7)), 
  (@@DATEFIRST-8)%7) AS DatefirstAsFirstDayOfWeek;

Primeiro, vamos explicar que @@DATEFIRST-8)%7 traduz o valor de DATEFIRST para o dia apropriado da semana, na virada de 1899/1900. (Você poderia criar uma expressão diferente que computasse o mesmo valor.) Aqui está uma tabela para mostrar quantos dias se passaram desde aquele ponto:

A outra expressão é

DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate - (@@DATEFIRST % 7)). 

Esta expressão calcula a diferença entre nosso dia da semana escolhido e a data de registro.

Como DATEDIFF() usa o domingo como primeiro dia da semana, temos que subtrair o valor de DATEFIRST da data de registro. Estamos "voltando" aos dias que, de acordo com DATEDIFF(), caem na semana seguinte.

Assim, para DATEFIRST = 3 (quarta-feira), domingo, segunda-feira e terça-feira devem ser "deslocados de volta" para a semana anterior para que DATEDIFF() funcione como pretendemos.