CODEWITHSUNDEEP
mysqlvariablesstored-procedures
Java
Java

Reputation: 2489

Store IN parameter in variable of Stored procedure

I am creating a Stored procedure in which ,

I am passing 2 IN parameters as 'year' & 'month' to a procedure,

then " I need to store these both IN parameters in one variable in date_format like ('YYYY-MM-DD')."

And after that I want to use that variable with that Date_Format.

I will try to explain above things in stored procedures format

CREATE PROCEDURE build_table(IN years CHAR(5) ,IN months CHAR(4))
BEGIN
DECLARE format1 date ;

# Now i want to store both IN parameters in 'format1' variable and then format1 should be in format 'YYYY-MM-DD'

Can I do this in stored procedure?

If yes give me some guidance.

Upvotes: 0

Views: 1468

Answers (1)

Devart
Devart

Reputation: 121922

Tru to use this variant -

SET @year = 2012;
SET @month = 2;
SELECT STR_TO_DATE(CONCAT(@year, '-', @month), '%Y-%c');

The STR_TO_DATE function will help you to make a date value. Change @year and @month variables with IN parameters.

Then you can use a DATE_FORMAT function to format datetime value, e.g.:

SET @year = 2012;
SET @month = 2;
SET @dt = STR_TO_DATE(CONCAT(@year, '-', @month), '%Y-%c');
SELECT DATE_FORMAT(@dt, '%m/%d/%Y');
+------------------------------+
| DATE_FORMAT(@dt, '%m/%d/%Y') |
+------------------------------+
| 02/00/2012                   |
+------------------------------+

CREATE PROCEDURE build_table(IN years CHAR(5), IN months CHAR(4))
BEGIN
  DECLARE format1 date;
  SET format1 = DATE_SUB(STR_TO_DATE(CONCAT(years, '-', months, '-01'), '%Y-%c-%e'), INTERVAL 1 DAY);


  SELECT format1;
END

CALL build_table(2012, 1);
+------------+
| format1    |
+------------+
| 2011-12-31 | -- default format is 'YYYY-MM-DD'
+------------+

Upvotes: 1

Related Questions