Get every day in a month dynamically MySQL – Stored Procedure

I share one MySQL Stored Procedure for every day of a month from a date.

At the moment I dont have a wordpress plugin for compatir code and is displayed in a nice way: D, so I share a picture of the Stored Procedure and below the Stored Procedure code itself.

dias_del_mes_dinamico

The logic is simple procedure, the stored parameter expects a start date month entry ‘2000-02-01’ format. Considering the input parameter must know what is the last day of that month, for which we use the function LAST_DAY('2000-02-01') returns us to ‘2000-02-28’. Knowing what the initial day and the final day we can go all dates that month and store in a temporär table, why a temporary table? Because YOLO: D.

Stored code here: D


#-----------------------------INICIO-------------------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `dias_mes_dinamico`(IN `dia_entrada` DATE)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
/*
Author: Rafael Vázquez
Date: 2015-08-01
Description: Procedure to get every day of the month
*/

#Declaration of variables
DECLARE var_inicio, var_final INT DEFAULT 0;
DECLARE fecha_Inicial,fecha_Final, fecha_incremento DATE;

#AVariable initializations
SET fecha_Inicial = dia_entrada;
SET fecha_Final= LAST_DAY(fecha_Inicial);
SET var_inicio = EXTRACT(DAY FROM fecha_Inicial);
SET var_final = EXTRACT(DAY FROM fecha_Final);
SET fecha_incremento = fecha_Inicial;

#Create a temporaty table to store every day of a month
CREATE TEMPORARY TABLE examen.tmp_dias_mes(
id INT,
dia_mes DATE
);

#Get every day of the month
WHILE var_inicio <= var_final DO
INSERT INTO examen.tmp_dias_mes (id,dia_mes) values(var_inicio, fecha_incremento);
SET fecha_incremento = DATE_ADD(fecha_incremento, INTERVAL 1 DAY);
SET var_inicio = var_inicio + 1;
END WHILE;

#Show the temporary table
SELECT * FROM examen.tmp_dias_mes;

#Drop the temporary table
DROP TABLE examen.tmp_dias_mes;
END
#—————————–FIN————————————————-

Well, you can still optimize this process, and we could validate that a date is valid and not to exploit the world when we pass February 30th: D and catch any exception.

I hope you find it useful. Regards.

P.D. Linux Rules!

Anuncios

Deja una respuesta:

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

w

Conectando a %s