Cómo obtener el primer día de la semana en SQL Server

Este es un contenido adicional para el curso LearnSQL.es Análisis del Comportamiento del Cliente en SQL Server.

En el artículo anterior, hablamos de cómo se puede utilizar la función DATEPART() de SQL Server con semana o iso_week para agrupar eventos por semana. La consulta podría tener el siguiente aspecto:

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);

El resultado es algo parecido a esto:

La consulta muestra el número de semana como etiqueta de la semana. Esta etiqueta no es muy útil. Después de todo, ¿cómo sabemos qué significa "semana 22"? ¿Es en abril, mayo o junio? Es mejor mostrar una fecha asociada a cada semana, es decir, su primer día.

En este artículo, le mostraremos cómo obtener el primer día de la semana en SQL Server. En primer lugar: un atajo.

El truco: Usar la función MIN()

Antes de hablar de las formas adecuadas de calcular el primer día de la semana en SQL Server, vamos a hablar de un truco que puede utilizar para mostrar una etiqueta legible para una semana: la función 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);

El resultado tendrá este aspecto:

Esta consulta muestra el número y la fecha mínima de registro de cada semana (en otras palabras, el primer día de la semana). Por supuesto, esta solución no es en absoluto perfecta; supone que los eventos que se cuentan (en este caso, las inscripciones) se producen todos los días. Si no hay inscripciones el primer día de la semana, la consulta le mostrará el segundo día de la semana como etiqueta WeekStart.

Esta solución puede ser suficiente si simplemente está trabajando de forma interactiva con los datos y necesita una aproximación de cuándo ocurrió un evento. Sin embargo, si necesita la fecha exacta del primer día de la semana, esto no servirá. Intentemos otra cosa.

Cómo calcular las etiquetas de semana en SQL Server

Hay dos formas comunes de definir el primer día de la semana: El domingo (normalmente utilizado en los Estados Unidos) y el lunes (normalmente utilizado en Europa). Empezaremos discutiendo cómo puede encontrar el primer día de la semana en cualquiera de esos estilos.

Opción 1: el domingo como primer día de la semana

Empezaremos por el domingo, ya que es más fácil de explicar. Esta es la expresión:

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

La función DATEADD() toma tres argumentos: un datepart, un número y una fecha. A continuación, añade un valor numérico especificado a la parte de fecha especificada de un valor de fecha de entrada y, posteriormente, devuelve ese valor modificado.

En la expresión anterior, añadimos un número determinado de semanas a la fecha -1. ¿Qué significa esto? Bueno, la fecha 0 es la medianoche del 1 de enero de 1900, que resulta ser un lunes. Así que la fecha -1 es el domingo 31 de diciembre de 1899. La expresión anterior añade un cierto número de semanas a esta fecha.

El argumento del número en nuestra expresión se calcula utilizando la función DATEDIFF(). DATEDIFF() también toma tres argumentos: la parte de la fecha, la fecha de inicio y la fecha final. Devuelve el recuento de las partes de fecha especificadas entre la fecha de inicio y la fecha final. Cuando se utiliza con el argumento de la semana, DATEDIFF() funciona independientemente de la configuración de DATEFIRST: siempre utiliza el domingo como primer día de la semana.

La expresión DATEDIFF(week, -1, RegistrationDate) calcula el número de semanas entre el domingo 31 de diciembre de 1899 (la fecha -1) y la RegistrationDate.

Esta expresión:

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

... toma el número de semanas entre el domingo 31 de diciembre de 1899 y la fecha de registro, suma ese número a ese domingo y, finalmente, devuelve el domingo (en otras palabras, el comienzo de la semana en que se produjo el registro).

Opción 2: el lunes como primer día de la semana

Veamos ahora una expresión que devuelve el lunes como primer día de la semana:

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

En la expresión anterior, añadimos el número de semanas especificado a la fecha 0. Como recuerda, el 0 representa la medianoche del lunes, 1 de enero de 1900.

La función DATEDIFF() trata el domingo como el primer día de la semana, independientemente de la configuración de DATEFIRST. Este es el calendario de enero de 1900 que utiliza DATEDIFF():

La expresión DATEDIFF(week, 0, RegistrationDate - 1) calcula el número de semanas entre el lunes 1 de enero de 1900 (fecha 0) y un día antes de la RegistrationDate. El desplazamiento de un día hacia atrás es necesario porque DATEDIFF() utiliza el domingo como primer día de la semana.

Veamos como ejemplo el domingo 7 de enero de 1900. La expresión DATEDIFF(week, 0, '19000107') devolverá 1; si se trata el domingo como primer día de la semana, el 7 de enero está en la semana 2. Sin embargo, queremos que el 7 de enero se trate como si estuviera en la semana 1, como si el lunes fuera el primer día de la semana. Por lo tanto, tenemos que "retroceder" un día para obtener el número correcto de semanas para una fecha.

Al final, esta es la expresión que obtenemos:

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

BONUS: Cómo calcular el primer día de la semana según la configuración de DATEFIRST

Si quiere ver una expresión que funcione correctamente para todas las configuraciones de DATEFIRST, tengo una mala noticia para usted: no es bonita. En la práctica, probablemente sólo considerará el lunes o el domingo como primer día de la semana. Pero, para completar este artículo, he aquí una expresión que funcionará correctamente independientemente de DATEFIRST:

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

En primer lugar, expliquemos que @@DATEFIRST-8)%7 traduce el valor de DATEFIRST al día de la semana apropiado en el cambio de 1899/1900. (Podrías idear una expresión diferente que calcule el mismo valor). Aquí tienes una tabla que te muestra cuántos días han transcurrido desde ese momento:

La otra expresión es

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

Esta expresión calcula la diferencia entre el día de la semana elegido y la fecha de registro.

Como DATEDIFF() utiliza el domingo como primer día de la semana, tenemos que restar el valor de DATEFIRST a la fecha de registro. Estamos "desplazando hacia atrás" los días que, según DATEDIFF(), caen en la semana siguiente.

Así, para DATEFIRST = 3 (miércoles), el domingo, el lunes y el martes deben ser "desplazados hacia atrás" a la semana anterior para que DATEDIFF() funcione como pretendemos.