El trabajar con fechas cuando de bases de datos se trata puede dar lugar a un dolor de cabeza si no se tiene en cuenta ciertos aspectos. Por eso en esta entrada te enseñaré cómo debes hacer tus consultas al trabajar con fechas en el lenguaje de consulta SQL. Arrancamos!

Formato Datetime ANSI SQL

Lo primero que debes saber es qué es el formato ANSI para el manejo de fechas en SQL, y se trata de un estándar que especifica cómo se deben representar los caracteres de los valores de fecha y tiempo.

Este estándar va de año a segundo y tiene la siguiente apariencia:

YYYY-MM-DD HH:MM:SS

También se puede obviar los guiones en el año, mes y día quedando YYYYMMDD

Si quieres más información de este estándar plasmado en la web de IBM puedes leerla aquí.

Este formato es el que debemos utilizar para hacer nuestras consultas SQL, esto quiere decir que si por ejemplo queremos obtener todas las órdenes de venta iguales o posteriores al 5 de enero del 2023, tendríamos que hacer una consulta de la siguiente forma:

SELECT * FROM Orders where OrderDate >= '20230105'

Esto quiere decir que evitemos utilizar formatos regionales DDMMYYYY o MMDDYYYY ya sea con guiones o con slash como por ejemplo:

05-01-2023

01-05-2023

05/01/2023

01/05/2023

Porque si hacemos esto estamos a merced de la configuración regional que tenga el motor de la base de datos e inclusive del host pudiendo confundir un 5 de enero con un primero de mayo, esto sería fatal 🙃.

ATENCIÓN! 😱

Existe una trampa cuando de fechas se trata y es que si en nuestra base de datos tenemos datos que incluyen segundos, cuando queremos obtener los registros de un día en particular, no basta con hacer uso del formato ANSI:

SELECT * FROM Orders WHERE OrderDate = '20230105'

Tenemos que hacer algo más, y esto te lo explico en la sección de abajo así que continúa leyendo😉

Hagamos consultas con SQL

Ahora vamos a SSMS y vamos a hacer algunas pruebas.

Ya que manejamos conocimientos de SQL entonces vamos a ejecutar algunas consultas, crearemos nuestra base de datos, luego nuestra tabla de ejemplo y la llenaremos con mucha data con fechas y al final haremos un pequeño taller con consultas que involucran fechas.

Está muy de moda aprender a hacer consultas de este tipo, tras el boom de la inteligencia de negocios o BI así que aprovecha este artículo 🫡

• Creamos una base de datos

USE MASTER
CREATE DATABASE DATETEST
USE DATETEST

• Creamos una tabla

CREATE TABLE Orders(
	Id int primary key identity,
	OrderDate datetime,
	SubTotal decimal(10,2)
)

• Llenamos la tabla con datos

DECLARE @TMP TABLE(
	OrderDate DATETIME,
	SubTotal DECIMAL(10,2)
)

declare @counter int = 1

while(@counter <= 10000)
begin
	INSERT INTO @TMP VALUES(
		  DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 946080000 , '1990-01-01')
			, CONVERT(DECIMAL(10,2), RAND()*1000)
	)

	SET @counter = @counter + 1
end

INSERT INTO Orders
select * from @TMP
order by orderdate

Para llenar a la tabla con datos hemos hecho uso de algunos conceptos intermedio-avanzados de SQL, que sería conveniente que los aprendas, sin embargo te comento brevemente qué hice:

Declaré una tabla como variable y luego hice uso de la iteración en SQL mediante la cláusula WHILE.

Hice 10 mil iteraciones y en cada una inserté un registro a la variable de tipo tabla llamado TMP

Inserté orderdates random entre 1990 y 2019 y subtotales random.

Ordené esos registros por fecha y los inserté a la tabla Orders.

Ahora tienes tu tabla llena de datos para que hagas las consultas a continuación y experimentes y compruebes por ti mismo lo que este artículo te enseña 😉

• Hacemos consultas diversas

Vamos a obtener las ventas para diferentes lapsos de tiempo, desde varios años hasta un día en particular.

--1:VENTAS DE LOS ULTIMOS 5 AÑOS
DECLARE @TMP TABLE (anio int)
declare @currentyear INT = (select year(GETDATE()))
declare @counter int = 0

while(@counter < 5)
begin
	INSERT INTO @TMP VALUES(@currentyear - @counter)

	set @counter = @counter + 1
end

SELECT * FROM Orders
WHERE YEAR(OrderDate) in (select anio from @TMP)

--2:VENTAS DEL AÑO 2015
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2015

--3:VENTAS DEL PRIMER TRIMESTRE 2015 (Q1 2015)
SELECT * FROM Orders
where YEAR(OrderDate) = 2015
and MONTH(OrderDate) IN (1,2,3)

--4:VENTAS DE ENERO 2015
SELECT * FROM Orders
where YEAR(OrderDate) = 2015
and MONTH(OrderDate) = 1

--5:VENTAS HECHAS ENTRE 11 JULIO 1997 Y EL 19 ABRIL 1998
SELECT * FROM Orders
where OrderDate between '19970711' and DATEADD(SECOND,-1, DATEADD(day,1,'19980419'))

SELECT * FROM Orders
where OrderDate >= '19970711'
and OrderDate < '19980420'

--6:VENTAS DEL 21 DE ENERO DE 1990
SELECT * FROM Orders
where OrderDate >= '19900121' and OrderDate < (DATEADD(DAY,1,'19900121'))

Me gustaría ahora comentarte dos cosas muy importantes.

Presta atención a las consulta 5

Nota que hay dos formas de hacerla, yo recomendaría la segunda.

El motivo es que si usas la cláusula BETWEEN tienes que tener en cuenta que obtiene todos los registros que hay entre dos fechas, incluyendo los extremos, y tiene en cuenta hasta los segundos.

Así que si quieres utilizar BETWEEN debes manejar muy bien esto de los segundos, y es por eso que en el extremo superior primero le añado un día al extremo superior, y después le resto 1 segundo para que me abarque hasta el último segundo del día anterior que viene a ser mi rango superior mismo.

Ahora presta atención a la consulta 6:

Pasa algo similar, si tus registros tienen segundos y quieres obtener las ventas de un día en particular no basta con hacer:

select * from orders where orderdate = '19900121'

ya que sólo te traerá los registros del 21 de enero de 1990 a las 0 horas 0 minutos y 0 segundos.

Sino que tendrás que aplicar el artificio descrito líneas arriba 😉

Y bueno eso es lo que quiero compartir con ustedes estimados devs. Es un tema que desde hace mucho he manejado en distintas oportunidades y ahora he podido plasmarlo en un artículo. Recuerdo que uno de los primeros blogs que leí sobre este tema fue hecho por Maximiliano Accotto en este artículo, aquella buenísima entrada me ayudó mucho en su momento y por eso quiero hoy poner mi grano de arena para todos ustedes caballeros y damas del código. 🫡

Si esta entrada te ha gustado compártelo crack! 🥳